This article will list new as well as existing Date and Time functions in SQL Server 2008.
The major changes in new SQL Server 2008 are the DateTime function and the introduction of new data types.
New data types are DATE, DATETIME2, TIME, DATETIMEOFFSET.
============================================================================
declare @DateTime DATETIME = GETDATE()
declare @Date DATE = GETDATE()
declare @DateTime DATETIME = GETDATE()
declare @Time TIME = GETDATE()
declare @OffSet DATETIMEOFFSET = GETDATE()
select @DateTime 'DateTime', @Date 'Date', @DateTime2 'DateTime2', @Time 'Time', @OffSet 'OffSet'
==============================================================
GETDATE()
Function returns the current system date and time.
select GETDATE()
SYSDATETIME()
This function works the same as GETDATE(); it returns date and time. The difference in both functions is that SYSDATETIME returns
a higher level of precision and also returns the newer datetime2 data type.
select SYSDATETIME()
CURRENT_TIMESTAMP
This function returns the current date and time as a datetime data type. Same as GETDATE().
select CURRENT_TIMESTAMP
GETUTCDATE()
This function returns the current UTC (Universal Time Coordinate) time or Greenwich Mean Time.
select GETUTCDATE()
SYSDATETIMEOFFSET()
This function returns the current system date and time. Instead of the simple datetime2 data type, however,
SYSDATETIMEOFFSET returns the time in the new datetimeoffset data type.
select SYSDATETIMEOFFSET()
SYSUTCDATETIME()
This function works the same as GETUTCDATE(), however, returns the newer datetime2 data type.
select SYSUTCDATETIME()
SWITCHOFFSET()
MSDN: Returns a datetimeoffset value that is changed from the stored time zone offset to a specified new time zone offset.
The function accepts two arguments, an input value of type datetimeoffset() and a new offset to represent the time.
select SWITCHOFFSET(<datetimeoffset data instance>, <newoffset time>)
TODATETIMEOFFSET()
This function accepts a given piece of date/time information and adds a provided time offset to produce a datetimeoffset data type.
select TODATETIMEOFFSET(<data that resolves to datetime>, <time zone>)
DAY()
This function returns an integer representing the day part of the specified date.
select DAY(<date>)
MONTH()
This function returns an integer representing the month part of the specified date.
select MONTH(<date>)
YEAR()
This function returns an integer representing the year part of the specified date.
select YEAR(<date>)
ISDATE()
This function determines whether an input expression is a valid date.
select ISDATE(<expression>)
DATEDIFF()
This function returns the difference between two specified dates in a specified unit of time.
select DATEDIFF(<datepart>, <startdate>, <enddate>)
DATENAME()
This function returns a string representing the name of the specified datepart of the specified date.
select DATENAME(<datepart>, <date>)
DATEPART()
This function returns an integer that represents the specified datepart of the specified date.
select DATEPART(<datepart>, <date>)
SQL Server recognizes eleven "dateparts" an their abbreviations.
Datepart |
Abbreviations |
year |
yy, yyyy |
quarter |
qq, q |
month |
mm, m |
dayofyear |
dy, y |
day |
dd, d |
week |
wk, ww |
weekday |
dw |
hour |
hh |
minute |
mi, n |
second |
ss, s |
millisecond |
ms |
Hope you all liked this listings, post your comments below.