SQL Server 2008 introduces four new DATETIME data types as follows:
DATE: – In Previous versions of SQL Server, we had to use the DATETIME or SMALLDATETIME data types even though if we had to store Date only. Thesetypes stores time component as part of it, which will appear as 12:00 AM. Then we needed to format our output to display only the date component. SQL Server 2008 introduces DATE data type, it is useful to store the dates without the time part, and it supports the Gregorian calendar and uses 3 bytes to store the date. The range for the DATE data type is from 01-01-0001 through 12-31-9999.
TIME: – Similar to the Date data type, there is a TIME data type in cases where if we need to store only the time. The range for the TIME data type is 00:00:00.0000000 through 23:59:59.9999999. The Time data type is accurate up to 100 nanoseconds, though it can be configured to be less accurate — and take up less space (anywhere from 3 to 5 bytes). The data type is not Time Zone aware and it is based on a 24 hour clock, it also is not aware of Day Light Saving.
DATETIMEOFFSET: – DATETIMEOFFSET is another new data type that is included in SQL Server 2008. We can store high precision date/ time with the DATETIMEOFFSET. We can’t store the Time Zone like Eastern Time, Central Time etc. in the data type but can store the offset -5:00 for EST and -6:00 CST and so on. The data type is not Day light saving aware. The date range is between 01-01-0001 and 12-31-9999 and the Time Range is between 00:00:00 and 23:59:59.9999999. The offset range is between -14:00 through +14:00. The precision of the data type can be set manually and it follows the Gregorian calendar. In nutshell, the new data type DATETIMEOFFSET defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock.
DATETIME2: – The reason of introducing DATETIME2 data type is, DATETIME is not SQL standard compliant, and DATETIME is not completely compatible with the .Net DATETIME type. DATETIME2 is basically a combination of the new DATE and TIME data types. The time portion is configurable again, and there are a lot of string formats that the DATETIME2 will accept. This data type also follows the Gregorian calendar and is not Day Light Saving Aware. The Time Zone can’t be specified in this data type. We have the option of specifying the number of fractions that we need. The maximum fraction we can specify is 7 while the minimum fraction is 0.
Apart from the changes in data type, SQL Server 2008 introduces five new functions: SYSDATETIME, SYSDATETIMEOFFSET, SYSUTCDATETIME SWITCHOFFSET and TODATETIMEOFFSET. The SYSDATETIME function returns the current system timestamp without the time zone, with an accuracy of 10 milliseconds. The SYSDATETIMEOFFSET function is the same is the SYSDATETIME function, however includes the time zone. SYSUTCDATETIME returns the Universal Coordinated Time (same as Greenwich Mean Time) date and time within an accuracy of 10 milliseconds. This is derived from the current local time and the time zone setting of the server where SQL Server is running. Both SYSDATETIME and SYSUTCDATETIME return DATETIME2 data type, whereas SYSDATETIMEOFFSET returns the DATETIMEOFFSET data type. SWITCHOFFSET functions return a DATETIMEOFFSET value that is changed from the stored time zone offset to a specified new time zone offset. The TODATETIMEOFFSET function converts a local date or time value and a specified time zone offset to a DATETIMEOFFSET value.
More information on New Date and Time Data Types can be found here: http://msdn.microsoft.com/en-us/library/ms186724(SQL.100).aspx
Regards
Arjit Malviya
Like us on FaceBook | Follow us on Twitter
Join the fastest growing SQL Server group on FaceBook
