In my last
month’s article, I talked about the new date and time data types that are
being introduced with SQL Server 2008. These new date and time data types
support an extended range of dates, and time that includes greater precision.
To manage and use these new date data types Microsoft has provided a number of
new system functions. In this article, I will be exploring these new system
functions and some of the old date functions that were available in older
releases of SQL Server.
SYSDATETIME
SQL Server 2008 allows you to store more precision (up to 7
decimal places) for the time portion of a date in some of the new data types.
The familiar GETDATE function, which you have been using to get the latest date
and time from the system, doesn’t support returning more than 3 decimal places
for the fractional portion of seconds. In order to get the additional
precision made available with these new date/time data types you need to use
the new SYSDATETIME function. Here is an example of how to use this function:
CREATE TABLE Product (ID int identity,
Product varchar(30),
ManufactureDate datetime2);
INSERT INTO Product values ('Widget',SYSDATETIME()),
('WingDing',SYSDATETIME());
SELECT Product,
cast(ManufactureDate as time) as [Manufacture TIME] FROM Product;
DROP TABLE Product;
When I run this code, I get the following output:
Product Manufacture TIME
------------------------------ ----------------
Widget 12:30:29.9696464
WingDing 12:30:29.9696464
Here I displayed the “Product” and the “Manufacture Time”
which is just the time portion of the DATETIME2 column named “ManufactureDate”.
This column was populated using the SYSDATETIME function in the INSERT
statement, just like you would using the old GETDATE() function. Using this function
allowed me to store 7 digits of precision in the ManufactureDate column.
SYSDATETIMEOFFSET
The SYSDATETIMEOFFSET function is used to return a DATETIMEOFFSET(7)
formatted date/time value. This function provides you with a method to
populate a DATETIMEOFFSET data type column with the current system date and
time with a time zone offset. Here is an example of how to use this function:
CREATE TABLE CallTicket (TicketID int identity,
TicketDesc varchar(max),
TicketDate datetimeoffset);
INSERT INTO CallTicket values ('Individual is having problem with...',SYSDATETIMEOFFSET()),
('Sofware crashes with a code 112233 ...',SYSDATETIMEOFFSET());
SELECT TicketID,
TicketDate FROM CallTicket;
DROP TABLE CallTicket;
When I run this, I get the following output:
TicketID TicketDate
----------- ----------------------------------
1 5/27/2008 5:41:23 PM -07:00
2 5/27/2008 5:41:23 PM -07:00
Here you can see my machine sets the TicketDate to my
machines local time, which is this case has time zone value of -07:00.
TODATETIMEOFFSET
The TODATETIMEOFFSET function changes the time zone of a
DATETIMEOFFSET data type. To use this function you pass two parameters. One
parameter is a DATETIMEOFFSET data type value and the other is a time zone
value. You might want to do this if you copy a database that stores the local
time in a DATETIMEOFFSET column to another system that has another time zone
and you want to make the date reflect the time zone of the new system. Here
is an example that uses this function:
SET NOCOUNT ON;
DECLARE @TD DATETIMEOFFSET;
SET @TD = SYSDATETIMEOFFSET();
SELECT @TD [Old Time Zone];
SELECT TODATETIMEOFFSET(@TD,'+06:00') [New Time Zone];
When I run this code on my machine, I get the following
output:
Old Time Zone
----------------------------------
6/12/2008 5:51:49 AM -07:00
New Time Zone
----------------------------------
6/12/2008 5:51:49 AM +06:00
In this code, I set the DATETIMEOFFSET variable to the
current system time, and then use the TODATETIMEOFFSET function to change the
time zone to +06:00. As you can see, the first SELECT statement has set the
time zone of the @TD variable to -07:00, which is my local time zone. Then
when I use the TODATETIMEOFFSET function the time zone changed to +06:00, it changes only the time zone value and not the actual time.
SWITCHOFFSET function
The SWITCHOFFSET function is similar to the TODATETIMEOFFSET
function, but instead of only changing the time zone value of a DATETIMEOFFSET
data type, this function also changes the time as well. When you use this
function the new date, time and time zone will be adjusted to reflect what time
it would be in the new time zone based on the date/time being changed. This
function is useful when displaying a date that matches a specific time zone
even when the base date contains a date that represents a different time zone.
This function will be great when trying to support an application that supports
users in multiple time zones. Here is an example that demonstrates how to use
this new function:
CREATE TABLE TimeZone (ID int identity,
LocalTime datetimeoffset);
INSERT INTO TimeZone values ('2008-05-21 17:50:01.1234567 +08:00'),
('2008-05-21 18:50:01.1234567 +08:00');
SELECT * FROM TimeZone;
SELECT ID, SWITCHOFFSET(LocalTime,'+06:00') [time for new time zone]
FROM TimeZone;
DROP TABLE TimeZone;
ID LocalTime
----------- ----------------------------------
1 5/21/2008 5:50:01 PM +08:00
2 5/21/2008 6:50:01 PM +08:00
ID time for new time zone
----------- ----------------------------------
1 5/21/2008 3:50:01 PM +06:00
2 5/21/2008 4:50:01 PM +06:00
Here you can see the first SELECT statement shows the original values of the LocalTime
column that have a time zone value of +08:00. Next, I display a different time
by switching the time zone of the LocalTime to +06:00 using the SWITCHOFFSET
function. As you can see this function not only changed the time zone, but it
also changed the time value to represent the new time zone. If you look at ID=1
the original time was 5:50.01 PM, after using the SWITCHOFFSET function, to
change the time zone by 2 zones (from +08:00 to + 06:00), the new LocalTime
value is now 3:50:01 PM.
Things to Think About When Using Other Date/Time Functions
With the new date/time date types that are available within
SQL Server 2008, SQL Server now supports dates from 0001-01-01 to 9999-01-01. You need to keep this in mind when using the old date/time functions that were
available in the old versions of SQL Server, and also available in SQL Server
2008. These functions may or may not work with the new date and time data
types. Below I will look at each of these old functions that were available in
SQL Server 2005, and discuss issues around using them along with the new date
and time data types.
ISDate Function
The ISDATE function is used to validate dates. This
function only works to validate SMALLDATETIME or DATETIME date types. This
function will return an error if you pass it on any of the new date related
data type. The ISDATE function can also be passed a string, but will only
identify that a string contains a valid date if the string contains a valid
DATETIME date value, meaning the string must fall within the following range: January 1, 1753 through December 31, 9999. So if you run the following code in SQL Server
2008:
SELECT CASE WHEN ISDATE('1752-12-31') = 1 THEN 'Valid Date' ELSE 'Invalid Date' END;
SELECT CASE WHEN ISDATE('1753-01-01') = 1 THEN 'Valid Date' ELSE 'Invalid Date' END;
You get the following output:
------------
Invalid Date
------------
Valid Date
As you can see, the date “1752-12-31” is not a valid date. You need to be aware of this when you start trying to validate strings
that contain dates that will have a destination data type of DATE, DATETIME2 or
DATETIME2OFFSET. There doesn’t seem to be a new function to validate those
extended dates for SQL Server 2008. If you normally validate dates using the
ISDATE function prior to storing them in a date/time data type you need to be
aware that this function will not validate all dates which are valid for DATE,
DATETIME2, or DATETIME2OFFSET data type.
DATEADD Function
The DATEADD function also does not fully support the new
date/time data types. Although this function will support the new date data
types if you call the function a specific way. Let me explain.
In the CTP 6 version of SQL Server 2008 if you pass a string
that contains a valid date string the DATEADD function fails. So, if you want
use this function to perform the date arithmetic operations for the new valid
date ranges you need to provide the function with a variable or column, and not
a string. Let me demonstrate.
In SQL Server 2005 and before (and SQL Server 2008 for that
matter) you could run the following code to add 1 day to a specific date
without getting an error.
DECLARE @d datetime
SET @d = '1900-01-01'
SELECT DATEADD(DAY,1,@d), DATEADD(DAY,1,'1900-01-01')
You can run the code above in SQL Server 2008 and it will
run just fine. But if you change the code slightly to look like this and run
it in SQL Server 2008:
DECLARE @d datetime2
SET @d = '0001-01-01'
SELECT DATEADD(DAY,1,@d), DATEADD(DAY,1,'0001-01-01')
You get the following conversion error:
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
You get this error because the DATEADD function can’t
convert ‘0001-01-01’ to a valid datetime data type, because it is not a valid
date that for that date type. But this code is valid:
DECLARE @d datetime2
SET @d = '0001-01-01'
SELECT DATEADD(DAY,1,@d)
This means you need to be careful when dealing with
the DATEADD function. This functionality holds true for both the DATE and
DATETIMEOFFSET data types. Make sure you don’t use literal strings that
contain dates that are only valid for the new date data types.
DATEDIFF Function
The DATEDIFF function allows you to compare two
dates and return the difference. This function seems to accept the new data
type that contains a date that wasn’t supported prior to SQL Server 2008.
Here is an example of where I can pass a DATETIME2 value and have it correctly
product the difference between two dates.
DECLARE @d1 DATETIMEOFFSET;
SET @d1 = '1700-04-01';
DECLARE @d2 DATETIMEOFFSET;
SET @d2 = '1701-04-01';
SELECT DATEDIFF(DAY,@d1,@d2) Variable_Diff, DATEDIFF(DAY,'1700-04-01','1701-04-01') String_Diff;
When I run this code, I get this output:
Variable_Diff String_Diff
------------- -----------
365 365
Here you can see passing a variable or a literal
string works.
Beware of Date Function
Using the date functions available in SQL Server 2008 a person needs to understand how they are affected when using them with any of the new date/time
data types. You need to beware of those functions that were available in SQL
Server 2005 that either don’t work with new data types, or only work a specify
way. By not understanding how the new date/time data types affect these older
functions you might find your application errors out, or gets unexpected
results. Now that SQL Server supports larger precision and time zone
information you need to make sure you use the correct new SQL Server functions
in order to populate your date correctly.
»
See All Articles by Columnist Gregory A. Larsen
» 
Comment and Contribute
