SQL Server 2008 New DATETIME DataTypes

This article is going to cover the newly introduced data types in SQL Server 2008. The DATETIME function’s major change in SQL Server 2008 is the four DATETIME data types introduced. They are DATE, TIME, DATETIME2 and DATETIMEOFFSET.

DATE Data Type

In SQL Server 2005, there is no data specific datatype to store only a Date. You must use the DATETIME or SMALLDATETIME data types. In addition to the date you have entered, you will see a time component, which will appear as 12:00 AM. You then need to format your output to display only the date component. Most of the time you can use the getdate() function to store the current date. If you save the getdate() value in a SMALLDATETIME or DATETIME column in SQL Server 2005, you will also store the current time, which may lead many issues.

In SQL Server 2008 we get a new DATE date type that allows you to store a date without a time. Notice that by default the date is displayed in YYYY-MM-DD format. The range for the DATE datatype is from 0001-01-01 through 9999-12-31. The DATE data type only takes three bytes to store its values as compared to eight bytes for a DATETIME and it accepts date values, consisting of year, month, and day. No parameters are required when declaring a DATE data type.

TIME Datatype

Similar to the Date datatype, there is a TIME datatype in cases where you need to store only the time. Defines a time of a day. The time is without time zone awareness and is based on a 24-hour clock. The range for the TIME data type is 00:00:00.0000000 through 23:59:59.9999999. The TIME data type accepts time values, consisting of hours, minutes, and seconds. No parameters are required when declaring a TIME data type. Date values should be specified in the form: HH:MM:SS.

DATETIME2 Data Type

The new DATETIME2 datetype is a date/time datatype with larger fractional seconds and year range than the existing DATETIME datatype. You have the option of specifing the number of fractions that you need. The maximum fraction you can specify is 7 while the minimum fraction is 0. In SQL Server 2008 a new datatype datetime2 can support storing of date values from the year 0001 to 9999.

DATETIMEOFFSET Datatype

Currently when saving the date and time in a column, it will not indicate what time zone that date and time belongs to. This can be especially important when you are dealing with data including several different countries with different time zones. The new datatype 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.

A time zone offset specifies the zone offset from UTC for a time or datetime value. The time zone offset can be represented as [+|-] hh:mm:

  • hh is two digits that range from 00 to 14 and represent the number of hours in the time zone offset.
  • mm is two digits, ranging from 00 to 59, that represent the number of additional minutes in the time zone offset.
  • + (plus) or – (minus) is the mandatory sign for a time zone offset. This indicates whether the time zone offset is added or subtracted from the UTC time to obtain the local time. The valid range of time zone offset is from -14:00 to +14:00.

The Datetimeoffset data type was introduced in SQL Server 2008 (and .Net Framework 3.5) and is the most advanced datetime date type available. It is made up of a date time and offset relative to the UTC time zone.

The following example compares the results of casting a string to each date and time data type.

SELECT

CAST(getdate() AS time(7)) AS ‘Time’

,CAST(getdate() AS date) AS ‘Date’

,CAST(getdate() AS smalldatetime) AS ‘Smalldatetime’

,CAST(getdate() AS datetime) AS ‘Datetime’

,CAST(getdate() AS datetime2(7)) AS ‘datetime2’

,CAST(getdate() AS datetimeoffset(7)) AS ‘Datetimeoffset’;

Here is the result set.

Data type

Output

Time 23:35:29. 1234567
Date 2011-07-26
Smalldatetime 2011-07-26 23:35:00
Datetime 2011-07-26 23:35:29.123
datetime2 2011-07-26 23:35:29. 1234567
Datetimeoffset 2011-07-26 23:35:29.1234567 +12:15
Advertisements
This entry was posted in SQL and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s