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.

Pages: 1 2




Related Articles :

  • No Related Articles Found

2 Responses to “SQL Server 2008 New DATETIME DataTypes”

  1. When I execute a select with SYSDATETIME() it gives me date 2 days in the past!!! 3 of the date functions gives me dates in the past and 3 give me the correct date. See below.

    SELECT SYSDATETIME(), SYSDATETIMEOFFSET(), SYSUTCDATETIME(), CURRENT_TIMESTAMP, GETDATE(), GETUTCDATE()
    ==================================
    2011-10-17 10:41:00.4521484
    2011-10-17 10:41:00.4521484 -04:00
    2011-10-17 14:41:00.4521484
    2011-10-19 10:41:00.447
    2011-10-19 10:41:00.447
    2011-10-19 14:41:00.45

    I am using MS SQLServer 2008 and the JDBC 3.0 driver:

    DatabaseProductName: MICROSOFT SQL SERVER DriverName: Microsoft SQL Server JDBC Driver 3.0
    getDatabaseProductName: Microsoft SQL Server
    getDatabaseProductVersion: 10.50.1600
    getDriverVersion: 3.0.1301.101
    getDriverMajorVersion: 3
    getDriverMinorVersion: 0
    getDriverName: Microsoft SQL Server JDBC Driver 3.0

    Any ideas what is going on here?

  2. Hi DINESH
    Thanks for nice article.
    But i would like to ask you how can i add offset in SYSDATETIMEOFFSET() function ?

    Likewise i want to add +2 in my currenttimezone so can i do like this way ?

    SYSDATETIMEOFFSET() + ’2.0′ OR
    SYSDATETIMEOFFSET(’2′)

    I did it but throw error.Will you please suggest for the same ?

    Thanks & Regards,
    Pushkar

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |