SQL Server 2008 New DATETIME DataTypes

SWITCHOFFSET

SWITCHOFFSET functions return a DATETIMEOFFSET value that is changed from the stored time zone offset to a specified new time zone offset.

SELECT SYSDATETIMEOFFSET(),SWITCHOFFSET (SYSDATETIMEOFFSET(), '-14:00')

The above script will return two columns. The first column will returen 2007-10-31 22:55:04.4286384 +05:45, which is the current date and time with UTC. The second column will return 2007-10-31 03:10:04.4286384 -14:00 by changing the date time value with given give time zone offset.

TODATETIMEOFFSET 

The TODATETIMEOFFSET function converts a local date or time value and a specified time zone offset to a datetimeoffset value.

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

The output of the above script will be 2007-10-31 23:08:45.137 +11:00. You can see that time zone is added to the output.

Conversion

The CONVERT function in SQL Server 2005 can be used to extract a date or time from the DATETIME component. This is a feature that was very much lacking in SQL Server 2005 and  in previous versions.

SELECT CONVERT(date, GETDATE()),CONVERT(time, GETDATE())

The first column will return 2007-10-31 while second column will return 23:35:59.1800000.

Issues with new DATETIME Data Type

In case you need to add DATE and TIME columns, you cannot add them like SMALLDATETIME datatypes. Attempting to do this will result in the following error message: Operand data type date is invalid for add operator.

You could also attempt to convert both fields to float, add them together and convert the result into the SMALLDATETIME or DATETIME column. (Bare in mind that the SMALLDATETIME data is stored as a float, the date value is the numeral part while time is the decimal part) However, this will also result in an error message: Explicit conversion from data type date to float is not allowed.

The correct way to do this is by converting both fields into SMALLDATETIME and add them together. You can see the output  from the following script.

Declare @dt as DATE

Set @dt = getdate()

Declare @dtt as TIME

Set @dtt = getdate()

Select cast(@dt as smalldatetime)  + cast(@dtt as smalldatetime)

Output: 2007-10-28 00:17:00. 

Points to Remember

At some point I am sure you will want to download SQL Server 2008. You can download this from the Microsoft site. One very important thing to remember is that you should not install both SQL Server 2005 and 2008 on the same platform. If you install SQL Server 2008 on  an operating system which has SQL Server 2005 installed, you will not be able to operate SQL Server 2005 afterwards. Therefore, make sure to install SQL Server 2008 on a server in which you are not intend to use SQL Server 2005 or in a virtual server.