Data Types Tips

I saw this tip on new date types provided with SQL Server 2008. I am interested in using the datetimeoffset because we support end users around the world. How do I convert the datetime data types to the datetimeoffset data type in SQL Server 2008?

When performing simple calculations inside of SQL Server using decimals, you can observe what may seem as odd behavior with the resulting datatype. Instead of returning the desired decimal format SQL Server generates an entirely different decimal format. In this tip we will look at the issue and how to resolve this.

There is sometimes a need to figure out the maximum space that is being used by a particular column in your database. You would initially think that the LEN() function would allow you to do this, but this function does not work on Text, NText or Image data types, so how do you figure out the length of a value in a column that has one of these data types?

Recently I did a migration for SQL Server 2008 from another platform. When I converted the database schema to SQL Server, I faced a problem converting TIMESTAMP to SQL Server. In MySQL/DB2, Timestamp is a datetime field. It is used to track changes to a record and updated every time the record is changed. In the migration process, we needed to store the datetime in the 'ModifiedDate' column and it should get updated every time the row is changed. But TIMESTAMP in SQL Server is a unique binary number within a database and used as a mechanism for version-stamping table rows, not for showing when a record was last updated. In this tip, we show how this behavior can be duplicated in SQL Server.

I have a column in one of my Microsoft SQL Server 2005 databases that store date and time data relating to date of birth. I'm only interested in the date of birth, not time of day, yet the data in this field stores both date and time. There are only two options for storing this data: either datetime or smalldatetime. No matter which one I use I end up with time information when I query the database table. I've heard that SQL Server 2008 offers more options for storing date and time values. Will any of these fit my needs?

Frequently surveys ask yes/no questions and use a bit datatype. What are the effects of allowing this column to be NULL in a SQL Server table? Check out this tip to learn more.

I have a column in one of my Microsoft SQL Server 2005 databases that store date and time data relating to date of birth. I'm only interested in the date of birth, not time of day, yet the data in this field stores both date and time. There are only two options for storing this data: either datetime or smalldatetime. No matter which one I use I end up with time information when I query the database table. I've heard that SQL Server 2008 offers more options for storing date and time values. Will any of these fit my needs?

There is sometimes a need to figure out the maximum space that is being used by a particular column in your database. You would initially think that the LEN() function would allow you to do this, but this function does not work on Text, NText or Image data types, so how do you figure out the length of a value in a column that has one of these data types?

Frequently surveys ask yes/no questions and use a bit datatype. What are the effects of allowing this column to be NULL in a SQL Server table? Check out this tip to learn more.

I saw this tip on new date types provided with SQL Server 2008. I am interested in using the datetimeoffset because we support end users around the world. How do I convert the datetime data types to the datetimeoffset data type in SQL Server 2008?

Recently I did a migration for SQL Server 2008 from another platform. When I converted the database schema to SQL Server, I faced a problem converting TIMESTAMP to SQL Server. In MySQL/DB2, Timestamp is a datetime field. It is used to track changes to a record and updated every time the record is changed. In the migration process, we needed to store the datetime in the 'ModifiedDate' column and it should get updated every time the row is changed. But TIMESTAMP in SQL Server is a unique binary number within a database and used as a mechanism for version-stamping table rows, not for showing when a record was last updated. In this tip, we show how this behavior can be duplicated in SQL Server.

When performing simple calculations inside of SQL Server using decimals, you can observe what may seem as odd behavior with the resulting datatype. Instead of returning the desired decimal format SQL Server generates an entirely different decimal format. In this tip we will look at the issue and how to resolve this.

Frequently surveys ask yes/no questions and use a bit datatype. What are the effects of allowing this column to be NULL in a SQL Server table? Check out this tip to learn more.

Recently I did a migration for SQL Server 2008 from another platform. When I converted the database schema to SQL Server, I faced a problem converting TIMESTAMP to SQL Server. In MySQL/DB2, Timestamp is a datetime field. It is used to track changes to a record and updated every time the record is changed. In the migration process, we needed to store the datetime in the 'ModifiedDate' column and it should get updated every time the row is changed. But TIMESTAMP in SQL Server is a unique binary number within a database and used as a mechanism for version-stamping table rows, not for showing when a record was last updated. In this tip, we show how this behavior can be duplicated in SQL Server.

I saw this tip on new date types provided with SQL Server 2008. I am interested in using the datetimeoffset because we support end users around the world. How do I convert the datetime data types to the datetimeoffset data type in SQL Server 2008?

When performing simple calculations inside of SQL Server using decimals, you can observe what may seem as odd behavior with the resulting datatype. Instead of returning the desired decimal format SQL Server generates an entirely different decimal format. In this tip we will look at the issue and how to resolve this.

I have a column in one of my Microsoft SQL Server 2005 databases that store date and time data relating to date of birth. I'm only interested in the date of birth, not time of day, yet the data in this field stores both date and time. There are only two options for storing this data: either datetime or smalldatetime. No matter which one I use I end up with time information when I query the database table. I've heard that SQL Server 2008 offers more options for storing date and time values. Will any of these fit my needs?

There is sometimes a need to figure out the maximum space that is being used by a particular column in your database. You would initially think that the LEN() function would allow you to do this, but this function does not work on Text, NText or Image data types, so how do you figure out the length of a value in a column that has one of these data types?