Building Time Zone awareness - Date Time types

I have been working for the last two years on projects which involve synchronising data between web applications and many a the date and time on web application used as the source of the replication is not on the same time zone as the destination web application. Some times it is necessary that some portion of the date time values moved across these applications be time zone aware. In other words some date time values need to be moved into the local time where the server is present, while some needed to be moved at absolute times(same as specified on source).

For eg.

Consider a table with four columns on a web application in UK on SQL 2000 server
ID - identity
Name - VARCHAR(100)
sellingDate - datetime -- The date and time on which the product should start selling
ModifiedDate - datetime -- The date and time on which the product record or data was last modified

Now when I move this data across to a web application in India the selling date should still remain on absolute date time specified, but the last modified time should be moved across as relative time to the time zone where this is present.

This is quite a constraint since you would end up having a business rule for every datetime column of your database and this rule is only a adhoc one determined by functionality rather than any real type based information. It would be great to distinguish these time zone aware columns in the database level.

Another problem when a .Net datetime value is serialised is it has an extra time offset appended at the end. This when passed to SQL 2000 parameters was not usable in its raw string form (say when passed as a XML fragment parameter containing this value)

In SQL 2008 we have new date time data types as below
DATE. - Allows only dates, accuracy is a day
TIME - Allows only times to the accuracy of nano seconds
SMALLDATETIME - Allows dates and times , accuracy is one second
DATETIME - Allows date and time as before with 3ms accuracy, but the range of dates that can be specified has been increased to 01-01-1753 to 31-12-9999
DATETIME2 - Allows specifying date and time like the DATETIME type but to a accuracy of nano seconds , YYYY-MM-DD hh:mm:ss:[nnnnnnn]
DATETIMEOFFSET - Allows users to specify a date and time with a time zone offset.

With these 6 new date time types in SQL server 2008, time zone aware applications can be more efficiently and easily designed/ developed.

As we have seen the date time types, lets see what functions are available, its great to see an introduction of the datetimeoffset but it would even better to do some playing around with it at the database level

Functions which return DATETIME values, These are as present in SQL 2000, 2005
GETDATE(), CURRENT_TIMESTAMP and GETUTCDATE() are still available.

Functions which return DATETIME2 values, other wise refered to as HIGH PRECISION date time functions, SYSDATETIME(), SYSUTCDATETIME(),SYSDATETIMEOFFSET() (returns DATETIMEOFFSET type value).

Other functions as in previous versions such as DATEPART, DATEDIFF, DATEADD are available , but two new useful functions which complement the DATETIMEOFFSET datatype hav been introduced.

SWITCHOFFSET() and TODATETIMEOFFSET()

e.g.

SELECT SWITCHOFFSET(sysdatetimeoffset(), '+08:00'

The SWITCHOFFSET function converts a value of datetimeoffset type from stored time zone to specified time zone

SELECT TODATETIMEOFFSET ( GETDATE() , '+08:00' )

The TODATETIMEOFFSET function converts a datetime type value into a DATETIMEOFFSET type with the offset specified

ISDATE()

Another useful function for datetime is the ISDATE() function, which validates that the specified value in the parameter is a valid date time value. There are several different scenarios to use it but in general for something as below

SELECT ISDATE(NULL); --returns 0


USE AdventureWorks;
GO
SELECT ISDATE(s.OrderDate) FROM Sales.SalesOrderHeader s;
GO
--Returns ones


All the information above is based on the November CTP for Katmai. Looking at all the above it does show good prospects for handling date and times for developers.