SQL Server 2008 New DATETIME DataTypes

By : Dinesh Asanka Nov 26, 2007

Printer friendly

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.

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

*/