TIME Data type:

TIME data type of SQL Server 2008 allows to exclusively storing the time.

The following is an example of using TIME data type:

DECLARE @dot as TIME

SET @dot = get date()

PRINT @dt

The above script displays the output as HH:MM:SS.SSSSSSS format. The TIME has the data range from 00:00:00.0000000 through 23:59:59.9999999.

DATETIME2 Data Type:

DATETIME2 is a data type which returns date/time. It provides larger factional seconds and the year compared to DATETIME data type. There are options to specify the number fractions as per the need. The maximum fraction is 7 and the minimum fraction is 0.

The following is an example of using DATETIME2 data type:

DECLARE @dt7 datetime2(7)

SET @dt7 = Getdate()

PRINT @dt7

The above script displays the date as YYYY-MM-DD HH:MM:SS.SSSSSSS format.

DATETIMEOFFSET Data type:

To store date, time along with time zone, the DATETIMEOFFSET is used. This is important when dealing with date of several countries with various time zones. The clock is based on 24-hour clock.

The following is an example of using DATETIMEOFFSET data type:

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)