SQL SERVER 2008 New Data Types - Date and Time

Today I am going to discuss about new 'date time' related datatypes introduced in sql server 2008. And what are the limitations  in earliler version of similar data types.

Date and Time:

In SQL SERVER 2005 and earlier SQL SERVER provides two ‘date time’ related data types only

·         DATETIME

·         SMALLDATETIME

They are very useful but there are some limitations of these data types:

·         Both data types store time along with date. When you need only time or date you need to manipulate your query. It costs performance to your query.

·         Storage wastage because you have to store the data which is not required (Time/Date).

·         It requires extra manipulation of query in time-zone related application, because these data types lack time-zone awareness.

·         Date range is not suitable for some of applications.

SQL SERVER 2008 introduces following four new ‘data time’ related data types to overcome these limitations.

1.    DATE:

·         The DATE data type stores a date in the format of YYYY-MM-DD.

·         Range for this data type is 0001-01-01 through 9999-12-32.

·         It is very useful for most business and scientific applications.

·         It takes 3 bytes to store the date.

2.     TIME:

·         TIME is stored in the format: hh:mm: ss.nnnnnnn.

·         Range of this data type is 00:00:00.0000000 through 23:59:59:9999999.

·         Storage depends on the precision and scale selected, and runs from 3 to 5 bytes.

3.    DATETIME2:

·         TIME is stored in the format: YYYY-MM-DD hh:mm:ss: nnnnnnnm.

·         Range of this data type 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999.

·         Storage depends on the precision and scale selected, and runs from 6 to 8 bytes.

4.    DATETIMEOFFSET:

·         It is similar to datatime2 but includes additional information to track the time-zone.

·         TIME is stored in the format: YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm.

·         Range of this data type 0001-01-01 00:00:00.0000000 through 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 (in UTC),

·         Storage depends on the precision and scale selected, and runs from 8 to 10 bytes.