SQL Server 2008 New DATETIME DataTypes

  • 1:12 AM
  • 1 comments

Introduction

SQL Server 2008 has arrived – not fully, but as a CTP version. Whenever you hear about new software, the first and most obvious question is “What are the new features?”. There are many new features and facilities in SQL Server 2008. This article is going to cover the newly introduced data types in SQL Server 2008 July CTP, and will specifically discuss the DATETIME functions.
The DATETIME function’s major change in SQL Server 2008 is the four DATETIME data types introduced. They are DATE, TIME, DATETIMEOFFSET and DATETIME2. IN addition to these newly introduced data types, there are new DATETIME functions all well. 

DATE Data Type
In SQL Server 2005, there is no data specific datatype to store only a Date. You must use the DATETIME or SMALLDATETIME data types. In addition to the date you have entered, you will see a time component, which will appear as 12:00 AM. You then need to format your output to display only the date component. Most of the time you can use the getdate() function to store the current date. If you save the getdate() value in a SMALLDATETIME or DATETIME column in SQL Server 2005, you will also store the current time, which may lead many issues. For example, if you want to search records for given date and you use
SELECT * FROM tblDate Where [Date] = ’2007-10-01′
It will not work properly because of the existing time component in Date column. Therefore, you need to use following query.
SELECT * FROM tblDate Where datediff(d,[Date],‘2007-10-01’) =0
While the above query will work, there is a high chance that the index that is  existing for the Date column will not be used. Still you can use the above query for a small number of records.
Although there are workarounds, it is very clear that there is a need for a DATE data type to reduce time and potential errors.
DECLARE @dt as DATE
SET @dt = getdate()
PRINT @dt
The output of the above script is 2007-10-27. As you can see, there is no time component. The range for the DATE datatype is from 0001-01-01 through 9999-12-31.
Unfortunately, the color of the DATE text is not blue, which is the default for all other datatypes. This may be a bug that needs to be fixed in coming CTPs. 

TIME Datatype

Similar to the Date datatype, there is a TIME datatype in cases where you need to store only the time.
The following is a sample query for using the TIME datatype.
DECLARE @dt as TIME
SET @dt = getdate()
PRINT @dt
The output of the above script is 23:48:04.0570000. The range for the TIME data type is 00:00:00.0000000 through 23:59:59.9999999. 


DATETIME2 Data Type

The new DATETIME2 datetype is a date/time datatype with larger fractional seconds and year range than the existing DATETIME datatype. You have the option of specifing the number of fractions that you need. The maximum fraction you can specify is 7 while the minimum fraction is 0. The following is an example of using DATETIME2.
DECLARE @dt7 datetime2(7)
SET @dt7 = Getdate()
PRINT @dt7
The result of above script is 2007-10-28 22:11:19.7030000.
The following is a list of outputs you get for each of the fractions.
Fraction
Output
0
2007-10-28 22:11:20
1
2007-10-28 22:11:19.7
2
2007-10-28 22:11:19.70
3
2007-10-28 22:11:19.703
4
2007-10-28 22:11:19.7030
5
2007-10-28 22:11:19.70300
6
2007-10-28 22:11:19.703000
7
2007-10-28 22:11:19.7030000



DATETIMEOFFSET Datatype

Currently when saving the date and time in a column, it will not indicate what time zone that date and time belongs to. This can be especially important when you are dealing with data including several different countries with different time zones. The new datatype DATETIMEOFFSET defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock.  The following script illustrates the usage of the DATETIMEOFFSET datatype.
DECLARE @dt DATETIMEOFFSET(0)
SET @dt = ’2007-10-29 22:50:55 -1:00′
DECLARE @dt1 DATETIMEOFFSET(0)
SET @dt1 = ’2007-10-29 22:50:55 +5:00′
SELECT DATEDIFF(hh,@dt,@Dt1)





DateTime Functions 

Currently we have the GETDATE function in SQL Server 2005 and SQL Server 2000 to retrieve the current date and time. Additionally, there are several other functions in SQL Server 2005, namely CURRENT_TIMESTAMP, DATEADD, DATEDIFF, DATENAME, DATEPART, DAY, GETUTCDATE, MONTH and YEAR. Apart from these functions, there are five new functions included in SQL Server 2008: SYSDATETIME, SYSDATETIMEOFFSET, SYSUTCDATETIME SWITCHOFFSET and TODATETIMEOFFSET. The SYSDATETIME function returns the current system timestamp without the time zone, with an accuracy of 10 milliseconds. The SYSDATETIMEOFFSET function is the same is the SYSDATETIME function, however includes the time zone.
SYSUTCDATETIME returns the Universal Coordinated Time (same as Greenwich Mean Time) date and time within an accuracy of 10 milliseconds. This is derived from the current local time and the time zone setting of the server where SQL Server is running. Both SYSDATETIME and SYSUTCDATETIME return DATETIME2 data type, where  SYSDATETIMEOFFSET returns the DATETIMEOFFSET datatype. Following is an example of the above datatypes. SELECT SYSDATETIME()
    ,SYSDATETIMEOFFSET()
    ,SYSUTCDATETIME()
    ,CURRENT_TIMESTAMP
    ,GETDATE()
    ,GETUTCDATE();
/* Returned:
SYSDATETIME()      2007-10-31 22:14:05.7131792
SYSDATETIMEOFFSET()2007-10-31 22:14:05.7131792 +05:45
SYSUTCDATETIME()   2007-10-31 16:29:05.7131792
CURRENT_TIMESTAMP  2007-10-31 22:14:05.710
GETDATE()          2007-10-31 22:14:05.710
GETUTCDATE()       2007-10-31 16:29:05.710
*/

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.  

Read more »
 

Copyright © 2010 SQL Cached, All Rights Reserved. Design by DZignine