Almost everybody who has worked a fair bit in SQL Server knows that the datetime data type in SQL Server is one of the most primitive. Infact, in a lot of applications I have seen, they don’t even see it worthy of being used, opting to use varchar instead. Their argument: If I am going to convert it into varchar anyways, then I might as well store it in the same format.
Hopefully, that will change with SQL Server 2008. DateTime datatype has been given special attention in the latest edition of SQL Server; so much so that I will stick my neck out and say that it is the only data type that has been enhanced!!! (FILESTREAM is not a data type, remember?). This post is an attempt to give you a preview of what the enhancements are in the DateTime datatypes.
In the previous versions of SQL Server, there were no separate datatypes for date and time. That has changed now. In SQL Server 2008, you have date and time datatypes that let you store only date or time. Apart from this, there is also the datetime2 and datetimeoffset. The datetime2 lets you store a wider range of dates. If you use datetime your minimum value for the year is 1/1/1753. The datetime2 datatype lets you store a wider range and is based on the ANSI standard. The datetimeoffset as you would have already guessed, helps you store dates for different regions. Some of the key points that you have to remember when using the above datatypes are
- You cannot use mathematical functions or expressions with the new datetime2 datatype. Use DateAdd instead.
- Try avoiding the format ‘dmy’. This is the only string format that produces different results with the old datetime and the new datetime2 format.
- Most of the old t-sql functions work consistently with both the datetime and the datetime2 datatypes.
- If you are using datetime2, use the SYSDATETIME function for more precision.
- Though you can use the Datetimeoffset datatype, SQL Server 2008 does not support automatic support for daylight savings.
- It does not also provide support for timezones, so the most effective way to use the offset is to store the timezone at which the date was entered.
- Use the SWITCHOFFSET function of SQL Server to convert the offsets to the timezones.
