Date and Time Data Type in SQL Server 2008

Date and Time Data Type in SQL Server 2008

In this post we will discuss about Date and Time Data Type in SQL Server 2008.

SmallDateTime: Defines date with the time of the day in 24-hour clock format. Requires fixed 4-bytes to store data. It can have date between January 1, 1900 through June 6, 2079 and time between 00:00:00 through 23:59:59.

DateTime: Defines date with the time of the day with fractional seconds in 24-hour clock format. Requires 8-bytes to store data. It can have date between January 1, 1753 through December 31, 9999 and time between 00:00:00:997.

DateTime2: This is new data type introduced in SQL Server 2008. It is an extension ofDateTime data type with larger values. Requires 6-8 bytes to stored data. It can have date between January 1,  1 AD through December 31, 9999 AD and time between 00:00:00 through 23:59:59:9999999.

Date: Defines a date. It doesn’t include time portion. Requires fixed 3-bytes to stored data. It can hold values betwenn January 1, 1 AD through December 31, 9999 AD.

Time: Defines a time of a day. Good thing is that it doesn’t entertain time zone issues. Requires fixed 5-bytes to stored data. It can hold values between 00:00:00:0000000 through 23:59:59:9999999.

DateTimeOffset: This is boon to database developers working with time zone requirement. It caters to time zone issues with 24-hours clock format. Required fixed 10-bytes to store data. It can hold values from January 1, 1 AD through December 31, 9999 AD and time between 00:00:00 through 23:59:59:9999999.