SQL Server 2008 New DATETIME Data Types

Introduction

In SQL Server Programming, we have been using Date & Time in many situations.In SQL Server 2008 the DATETIME function’s major changes is the four DATETIME data types introduced. They are DATE, TIME, DATETIMEOFFSET and DATETIME2. In this article we shall discuss about these functions in details with appropriate examples

DATE Data Type

In SQL Server 2005, there is no specific data type to store only a Date. For this we have use DATETIME or SMALLDATETIME data types. Suppose if you enter Date value, you will see a time component, which will appear as 12:00 AM. You need to format your output to display only the date component.
In SQL Server 2008, we have DATE data type.

DECLARE @dt as DATE

SET @dt = getdate()

PRINT @dt

The output of the above script will be 2009-02-18 (Current Date)

As you can see, there is no time component.

The range for the DATE datatype is from 0001-01-01 through 9999-12-31.

TIME Data Type

Like DATE datatype, there is TIME datatype. If you want store only time, you can use this 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

This new datatype is used store datetime with larger fractional seconds and year range than the existing DATETIME datatype. The maximum fraction is 7 while the minimum is 0.

DECLARE @dt7 datetime2(7) 

SET @dt7 = Getdate() 

PRINT @dt7 

The result of above script is 2009-02-17 22:11:19.7030000

DATETIMEOFFSET Data Type

In SQL Sever, there is no option to store time zone that date and time belongs to. The new data type DATETIMEOFFSET is eliminates this restriction.

DECLARE @dt DATETIMEOFFSET(0) 

SET @dt = '2009-02-17 22:50:55 -1:00' 

DATETIME Functions

GETDATE function in SQL Server 2000, 2005 is used to retrieve the current date and time. We have following functions in 2005,

• CURRENT_TIMESTAMP
• DATEADD
• DATEDIFF
• DATENAME
• DATEPART
• DAY
• GETUTCDATE
• MONTH
• YEAR

Apart from these functions, there are five new functions included in SQL Server 2008:

• SYSDATETIME : The SYSDATETIME function returns the current system timestamp without the time zone, with an accuracy of 10 milliseconds.
• SYSDATETIMEOFFSET : The SYSDATETIMEOFFSET function is the same is the SYSDATETIME function, however includes the time zone.
• SYSUTCDATETIME : SYSUTCDATETIME returns the Universal Coordinated Time (same as Greenwich Mean Time) date and time within an accuracy of 10 milliseconds.
• SWITCHOFFSET : SWITCHOFFSET functions return a DATETIMEOFFSET value that is changed from the stored time zone offset to a specified new time zone offset.
• TODATETIMEOFFSET : The TODATETIMEOFFSET function converts a local date or time value and a specified time zone offset to a datetimeoffset value.

Conclusion

Using the above functions, the various date and time related functionalities can be easily achieved.

Related Posts

1. An Introduction to Microsoft SQL Server 2008 – Part 1
2. SQL Server 2008 Top New Features
3. Automatic Backup Compression in SQL Server 2008
4. Transactions in SQL- Server 2008
5. Why Upgrade to Microsoft SQL Server 2008?

Tags: Datetime datypes, SQL datatypesm, SQL Server 2008, SQL Server 2008 datatypes