Introduction:
In this article,i am going to explain about how to use new date and time data types in sql server 2008.

Main:

The date, time, datetime2, and datetimeoffset types are four new data types in SQL Server 2008 for storing dates and times, which you should now begin using for new database development in lieu of the traditional datetime and smalldatetime data types. The new types are now much better aligned with the .NET Framework, Microsoft Windows, and the SQL standard—unlike datetime and smalldatetime—and have important advantages over those types, including improvements in range, precision, and storage.

Separation of Dates and Times
We’ll begin by looking at the new date and time types. Database developers have long been clamoring for the ability to store dates and times as separate types, and SQL Server 2008 now finally delivers it to us with these two new types. If you need to store only a date value (for example, a date of birth), use the new date type. Similarly, use the new time type for storing just a time value (for example, a daily medication time), as shown here:

DECLARE @DOB date
DECLARE @MedsAt time

The datetime and smalldatetime types, which were the only previously available options, each include both a date and a time portion. In cases where only the date or only the time is needed, the extraneous portion consumes storage needlessly, which results in wasted space in the database. In addition to saving storage, using date rather than datetime yields better performance for date-only manipulations and calculations, since there is no time portion to be handled or considered.

DECLARE @Time1 datetimeoffset DECLARE @Time2 datetimeoffset DECLARE @MinutesDiff int   SET @Time1 = '2007-11-10 09:15:00-05:00' -- NY time is UTC -05:00 SET @Time2 = '2007-11-10 10:30:00-08:00' -- LA time is UTC -08:00   SET @MinutesDiff = DATEDIFF(minute, @Time1, @Time2)   SELECT @MinutesDiff

The output result from this code is:

———–
255

(1 row(s) affected)

Conclusion:
Hope this helps,
Happy Coding.