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, well 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 CONVERTs 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 dont need seconds and your years are between 1900 and 2079, then the SMALLDATETIME type is an option. Its 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.
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.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 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