Introduction
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
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:
DECLARE @myTime AS DATETIME;
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
date:
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:
SELECT CONVERT(DATETIME, ‘Jan 1 2007 12:00AM’)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:
DECLARE @myDate AS DATETIME;
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).
DATETIME
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:
DECLARE @myDate DATETIME;
SET @myDate = ‘2007-10-13 12:35:29.998’;
SELECT @myDate;
Results: 2007-10-13 12:35:29.997
SELECT CAST(@myDate AS SMALLDATETIME);
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
values:
INSERT INTO Types
(standardDateTime)
VALUES
(‘2007-10-12 12:35:29.998’),
(‘2007-10-11’),
(GETDATE())
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:
SELECT *
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.
DATETIME2
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
below:
SELECT CAST(‘2007-10-20 20:30:05.1234567’ as DATETIME2)
Results: 2007-10-20 20:30:05.1234567SELECT 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-20SELECT CAST(‘2007-10-20 20:30:05.1234567’ as TIME)
Results: 20:30:05.1234567
DATETIMEOFFSET
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
Conclusion
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
20:30:00
CAST(‘2007-10-20 20:30:05.123’ as DATETIME) = 2007-10-20
20:30:05.123
CAST(‘2007-10-20 20:30:05.1234567 +5:0’ as DATETIME2(7)) = 2007-10-20
20:30:05.1234567
CAST(‘2007-10-20 20:30:05.1234567 +5:0’ as
DATETIMEOFFSET(7)) = 2007-10-20 20:30:05.1234567 +05:00
