More updates on Date Time Data Types

Let’s discuss about new data types introduced in SQL 2008 on date:

1. DATE: Date defines a date without time. Earlier we had datetime data type, which was used to store date values but unfortunately it used to store time also in it. Suppose we have a column DOB in the table, in this case if we have the data type as DATE.

For example: select cast (getdate() as DATE)
The result is 2008-06-04 00:00:00.000

The storage capacity of DATE data type is 3 bytes as compared to 8 bytes for a datetime data type.

2. TIME: Time extracts / stores the value of time. Similar to datetime data type, which had date and time values in it. Now we can store only time using TIME data type. The time is without time zone awareness and is based on a 24-hour clock

For example: select cast (getdate() as TIME)
The result is 10:23:00.5900000

The storage capacity of TIME data type is 5 bytes as compared to 8 bytes for a datetime data type.

3. DateTime2: Defines a date that is combined with a time of day that is based on 24-hour clock. DateTime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, and optional user-specified precision.

For example:
select cast (getdate() as DATETIME)
The result is 2008-06-04 10:25:06.123
select cast (getdate() as DATETIME2)
The result is 2008-06-04 10:25:06.123

The storage capacity of DateTime2 data type is 8 bytes as compared to 8 bytes for a datetime data type.

4. 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.

For example: select cast (getdate() as DATETIMEOFFSET)
The result is 6/4/2008 10:49:20 AM +00:00

The storage capacity of DateTimeOffset data type is 10 bytes as compared to 8 bytes for a datetime data type.

Date and Time Data Types
The Transact-SQL date and time data types are listed below:

Data type = Time
Format = hh:mm:ss[.nnnnnnn]
Range = 00:00:00.0000000 through 23:59:59.9999999
Accuracy = 100 nanoseconds
Storage size (bytes) = 3-5
User-defined fractional second precision = YES
Time zone offset = NO


Data type = Date
Format = YYYY-MM-DD
Range = 0001-01-01 through 9999-12-31
Accuracy = 1 day
Storage size (bytes) = 3
User-defined fractional second precision = No
Time zone offset = NO



Data type = datetime
Format = YYYY-MM-DD hh:mm:ss[.nnn]
Range = 1753-01-01 through 9999-12-31
Accuracy = 0.00333 second
Storage size (bytes) = 8
User-defined fractional second precision = No
Time zone offset = NO



Data type = datetime2
Format = YYYY-MM-DD hh:mm:ss[.nnnnnnn]
Range = 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999
Accuracy = 100 nanoseconds
Storage size (bytes) = 6-8
User-defined fractional second precision = Yes
Time zone offset = NO



Data type = datetimeoffset
Format = YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm
Range = 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 (in UTC)
Accuracy = 100 nanoseconds
Storage size (bytes) = 8-10
User-defined fractional second precision = Yes
Time zone offset = Yes