They are : DATE, TIME, DATETIMEOFFSET and DATETIME2. In addition to these newly introduce data types, there are new DATETIME functions also present.
DATE Data type:-
In SQL Server 2008, this data type is used to store only date part.
declare @dt as date
set @dt = GETDATE()
print @dt
The output of the above script is 2009-04-14.
Here you can see, there is no time component. The range for the DATE datatype is from 0001-01-01 through 9999-12-31.
The Biggest use of this will be in the queries that you want to find result sets based on a single date. In SQL 2005, we had to do a Between the Prev Date and Next Date or Use a DateDiff function that gets the date with 1 day difference or some other indirect ways. Now we can directly run a query with where clause that just gives the Date.
TIME Data type:-
Similar to the DATE datatype, there is a TIME datatype present where you can only store the time.
Ex:-
declare @dt as time(7)
set @dt = GETDATE()
print @dt
The output of the above script is 10:42:37.8300000.
The range for the TIME data type is 00:00:00.0000000 through 23:59:59:9999999.
You have the option of specifying the number of fractions that you need.
The maximum fraction you can specify is 7 while the minimum fraction is 0.
The new DATETIME2 datatype is a date/time datatype with larger fractional seconds and year range than the existing DATETIME datatype. Like TIME datatype here you have the option for specifying the number of fractions that you need. The maximum fraction you can specify is 7 while the minimum fraction is 0.
declare @dt as datetime2(3)
set @dt = GETDATE()
print @dt
The result of above script is 2009-04-14 11:30:33.457
Currently when we save the date and time in a column, it will not indicate what time zone that date and time belongs to. This 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.
This can be helpful when you are dealing with data including several different countries with different time zones.
declare @dt as datetimeoffset(5)
set @dt = GETDATE()
print @dt
The result of above script is 2009-04-14 11:34:16.60700 +00:00
There are few new functions included in SQL Server 2008:
SYSDATETIME, SYSDATETIMOFFSET, SYSUTCDATETIME, SWITCHOFFSET andTODATETIMEOFFSET.
SYSDATETIME()-
This function returns the current system timestamp without the time zone.
Ex:-SELECT SYSDATETIME()
Output — 2009-04-14 11:55:15.4163710
SYSDATETIMOFFSET()-
This function is same as SYSDATETIME(), however it includes the time zone.
EX:-SELECT SYSDATETIMEOFFSET()
Output — 2009-04-14 11:57:40.9148710 +05:30
SYSUTCDATETIME()-
This function returns the Universal Coordinated Time (same as GMT) date and time with milliseconds. This is derived from the current local time and the time zone setting of the server where SQL Server running. This returns DATETIME2 datatype.
EX:-SELECT SYSUTCDATETIME()
Output — 2009-04-14 06:28:02.9613115
SWITCHOFFSET () –
This function returns a DATETIMEOFFSET value that is changed from the stored time zone offset to a specified new time zone offset.
EX:- SELECT SYSDATETIMEOFFSET(),SWITCHOFFSET(SYSDATETIMEOFFSET(),’-14:00′)
Output– Here it returns two columns.
2009-04-14 12:03:23.3206900 +05:30 –> Current date and time (first column)
2009-04-13 16:33:23.3206900 -14:00 –> Changing the datetime value and give time zone offset (second column)
TODATETIMEOFFSET()-
This function converts a local date or time value and a specified time zone offset to a datetimeoffset value.
EX:-SELECT TODATETIMEOFFSET(GETDATE(),’+11:00′)
Output — 2009-04-14 12:07:29.227 +11:00 (time zone is added)