Date & Time Data Types in SQL Server 2008

The four new data types for storing dates and times in SQL Server 2008 are

• Date
• Time
• DateTime2
• DateTimeoffset

This post discuss about new date time data types and new date time related functions introduced in SQL Server 2008.

These data types are much aligned with the .NET Framework and have the better improvement in precision and storage. With the introduction of Date and Time data types we can store them as separate types. If you need to store only a date value(Example DOB) use new date type. Similarly use time type for storing time values.

DECLARE @DOB date
DECLARE @Appointment time

The available options in previous version are datetime and smalldatetime types, both includes date and time portion. The .NET Data Types which matched to SQL SERVER 2008 date and time types are System.DateTime and System.TimeSpan

If you want to store both date and time as a single value then you can use new data type datetime2.

The other data type in this category is datetimeoffset which defines the date and time with same range and precision as datetime2 and also includes the offset value which indicates the time zone. In earlier versions date and times are stores as in UTC format which requires conversion between UTC and local time. These calculations needs to be handled in application logic.

Now SQL Server handles the conversions automatically in the background. Using this datetimeoffset data type you can store your local time zone values.

DECLARE @Time1 datetimeoffset
DECLARE @Time2 datetimeoffset
DECLARE @MinutesDiff int
SET @Time1 = '2010-11-10 09:15:00-05:00' -- NY time is UTC -05:00
SET @Time2 = '2010-11-10 10:30:00-08:00' -- LA time is UTC -08:00

Time Zone names and Day light saving are not supported in this version.

The .NET FW also now provides the same functionality in a new type System.DateTimeOffset so .NET client applications and SQL server pass the values very easily.

You can use CONVERT function to extract just date and time portion of datetime2.

CREATE TABLE EMP(DOB datetime2)
-- Insert some rows into EMP Table...

SELECT DOB FROM EMP WHERE CONVERT(date, DOB) = '2005-04-07';
SELECT DOB FROM EMP WHERE CONVERT(time(0), DOB) = '09:00:00';

NEW and CHANGED Functions in SQL Server 2008

All the traditional functions DATEADD, DATEDIFF, DATEPART and DATENAME supports the new date and time data types in SQL Server 2008.

The two new functions added in this version are

SYSDATETIME and SYSUTCDATETIME new functions return the date and time on the server as datetime2 types.

SELECT GETDATE() AS 'GETDATE() datetime'
SELECT GETUTCDATE() AS 'GETUTCDATE() datetime'
SELECT SYSDATETIME() AS 'SYSDATETIME() datetime2'
SELECT SYSUTCDATETIME() AS 'SYSUTCDATETIME() datetime2'
SELECT SYSDATETIMEOFFSET() AS 'SYSDATETIMEOFFSET() datetimeoffset'

Share this post :