Rotating Header Image

SQL 2008 – New DateTime Datatypes

Finally SQL Server (well SQL Server 2008 anyways) gives you the option of storing your date and time separately.  In SQL 2008 there is a new set of datatypes for storing date, time, and both date and time.  The date limitations have also been changed so that now you can store from the beginning of time (0001-01-01) to the end of time (9999-12-31).

TIME

Pretty obvious what this new datatype stores.  This new datatype will store the time which includes hours, minutes, and seconds (fractional seconds up to 9999999)

Example Output: 06:38:12.1230000

DATE

The DATE datatype stores the date value that include Month, Day, and year (oh happy day).  The DATE datatype can go from 0001-01-01 to 9999-12-31.

Example Output: 1979-01-30

DATETIME2

Not so obvious by the name; this datatype is very similar to the old DATETIME format except for one difference and that is the range of dates that it can hold.  DATETIME2 (like DATE) can store dates from 0001-01-01 to 9999-12-31.  DATETIME2 stores both the date and time values.

Example Output: 1200-01-30 12:15:22.1234000

DATETIMEOFFSET

DATETIMEOFFSET is a timezone aware datatype.  DATETIMEOFFSET is a date that is combined with a time of a day that has timezone awareness.  The time zone offset range is -14:00 through +14:00.

Example Output: 1979-01-30 12:35:17.1234000 +12:15

Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

Leave a Reply

Twitter links powered by Tweet This v1.6.1, a WordPress plugin for Twitter.