SQL 2008 Date Types


This article introduces the new Date and Time data types
found in Microsoft SQL Server 2008, such as DATETIME2, TIME, and DATE. The
traditional data types that have been carried forward from previous versions
will also be reviewed. In addition, TSQL commands for manipulating data types,
such as CAST and CONVERT will be examined.


CAST and CONVERT will be covered first because these TSQL
functions will allow us to view the differences between data types more
easily. CAST and CONVERT are used to change a values data type or style. The
two functions are similar in purpose, but CONVERT has the ability to change Style.
Style can be used to change the number 4431.334 to a common money style of
4,431.33 (comma every three digits and two numbers after the decimal), or
change a four year 2007 to just a two year 07.

The syntax of CAST is CAST(value AS new type). For example,
we’ll declare a DATETIME then change it to a string:

SET @myTime = ‘1/1/2007’;
SELECT @myTime;

Results: 2007-01-01 00:00:00.000

SELECT CAST(@myTime as varchar(50));

Results: Jan 1 2007 12:00AM

We can also go the opposite way and convert a string to

SELECT CAST(‘Jan 1 2007 12:00AM’ as DATETIME)

Results: 2007-01-01 00:00:00.000

The syntax for CONVERT is CONVERT(new type, value, Style).
Style is optional. The previous CAST example can be rewritten to CONVERT as
shown below:


Results: 2007-01-01 00:00:00.000

The Style component is very handy for formatting an output.
To display a date in the common style of mm/dd/yy, just add a Style value to
the CONVERT as shown below:

SET @myDate = ‘1/1/2007’;

SELECT CONVERT(varchar(50), @myDate, 1)
Results: 01/01/07

To change the display so the month is spelled out, alter the
CONVERT’s Style from 1 to 7:

SELECT CONVERT(varchar(50), @myDate, 7)

Results: Jan 01, 07

The complete list of styles can be found in Books On Line
under CAST and CONVERT (Transact-SQL).


The standard DATETIME data type has been carried forward
into SQL Server 2008. Although a single type, it holds both a date and a time
component together. DATETIME is eight bytes in size, where the first four
bytes hold the date and the last four hold the time. The date component is
actually the number of days away from the internal system “base date” of January 1, 1990. This can be seen by passing an empty string in as a datetime value,
then looking at the results. Below, a table was created with a field called standardDateTime;
its type was DATETIME. An empty sting was passed in then retrieved.

INSERT INTO Types (standardDateTime) VALUES (”);
SELECT standardDateTime FROM Types;

Results: 1900-01-01 00:00:00.000

The column type DATETIME can hold year values between 1753
and 9999. The time component is accurate to 3.33 milliseconds. If you don’t
need seconds and your years are between 1900 and 2079, then the SMALLDATETIME
type is an option. It’s half the byte size of the DATETIME and seconds are
rounded to the nearest minute as shown below:

SET @myDate = ‘2007-10-13 12:35:29.998’;
SELECT @myDate;

Results: 2007-10-13 12:35:29.997
Results: 2007-10-13 12:35:00

The first result is the standard DATETIME. The second uses
the CAST function to display @myDate as a SMALLDATETIME.

A common DATETIME question involves errors when selecting
with only a date. For example, if we load a test table with the following three

(‘2007-10-12 12:35:29.998’),

Then issue a select looking for the date of the twelfth;

SELECT standardDateTime
FROM Types
WHERE standardDateTime = ‘2007-10-12’;

No rows will be returned because a time was included with
the insert. The same result will occur when inserting with the GETDATE()
function. We would need to match the time as well as the date. If no time were
included with the INSERT, as in the ‘2007-10-11’, then the select would work as shown below:

FROM Types
WHERE standardDateTime = ‘2007-10-11’

Results: 2007-10-11 00:00:00.000

As a side note, notice the previous INSERT. SQL 2008 now
permits multiple rows to be inserted within one statement.


New in SQL 2008 is the data type DATETIME2. This new type
is similar to a standard DATETIME but allows for greater precision. The year
range can accommodate values between Jan 1 year 1 AD to 12/31/9999. Time can
be valued out to seven decimal places of fractional seconds. Also, the clock
is a 24-hour cycle. So, 8 pm can be stored as the military time 20:00. The syntax for declaring this new type is either just DATETIME2, or DATETIME2(n).
Where (n) is a value between 0 and 7 to specify the fractional seconds as shown

SELECT CAST(‘2007-10-20 20:30:05.1234567’ as DATETIME2)
Results: 2007-10-20 20:30:05.1234567

SELECT CAST(‘2007-10-20 20:30:05.1234567’ as DATETIME2(4))
Results: 2007-10-20 20:30:05.1235

DATE and TIME Standalones

Now in SQL 2008 dates and times can be stored separately
rather than together. There is DATE data type and a TIME date type. The TIME
type can specify the fractional seconds like the DATETIME2 type. They are
demonstrated below:

SELECT CAST(‘2007-10-20 20:30:05.1234567’ as DATE)
Results: 2007-10-20

SELECT CAST(‘2007-10-20 20:30:05.1234567’ as TIME)
Results: 20:30:05.1234567


Values that need to be time zone aware can now be handled
natively in SQL 2008 with the new DATETIMEOFFEST data type. This new type
combines a DATETIME2 with an offset component as shown below:

SELECT CAST(‘2007-10-20 20:30:05.1234567 +5:0’ as DATETIMEOFFSET)
Results: 2007-10-20 20:30:05.1234567 +05:00

Like the DATETIME2 type, DATETIMEOFFSET has an option (n)
parameter that can specify the fractional seconds as show below:

SELECT CAST(‘2007-10-20 20:30:05.1234567 +5:0’ as DATETIMEOFFSET(3))
2007-10-20 20:30:05.123 +05:00


SQL 2008 includes some new and very handy DATE and TIME data
types. Below they are demonstrated side by side:

CAST(‘2007-10-20 20:30:05.1234567 +5:0’ as DATE) = 2007-10-20

CAST(‘2007-10-20 20:30:05.1234567 +5:0’ as TIME(7)) = 20:30:05.1234567

CAST(‘2007-10-20 20:30:05.123’ as SMALLDATETIME) = 2007-10-20

CAST(‘2007-10-20 20:30:05.123’ as DATETIME) = 2007-10-20

CAST(‘2007-10-20 20:30:05.1234567 +5:0’ as DATETIME2(7)) = 2007-10-20

CAST(‘2007-10-20 20:30:05.1234567 +5:0’ as
DATETIMEOFFSET(7)) = 2007-10-20 20:30:05.1234567 +05:00


See All Articles by Columnist
Don Schlichting

Don Schlichting
Don Schlichting
Don Schlichting is a Microsoft MSCE, MCDBA and an Oracle Certified Associate. Working at Professional Control Corporation as the IT Director,.
Get the Free Newsletter!
Subscribe to Cloud Insider for top news, trends & analysis
This email address is invalid.
Get the Free Newsletter!
Subscribe to Cloud Insider for top news, trends & analysis
This email address is invalid.

Latest Articles