Date and Time Functions in SQL Server

This article will list new as well as existing Date and Time functions in SQL Server.
 
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.
  1. declare @DateTime DATETIME = GETDATE()  
  2. declare @Date DATE = GETDATE()  
  3. declare @DateTime DATETIME = GETDATE()  
  4. declare @Time TIME = GETDATE()  
  5. declare @OffSet DATETIMEOFFSET = GETDATE()  
  6. select @DateTime 'DateTime', @Date 'Date', @DateTime2 'DateTime2', @Time 'Time', @OffSet 'OffSet'  

Date and Time functions in SQL Server

 
GETDATE()
 
Function returns the current system date and time.
  1. 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. 
  1. select SYSDATETIME()  
CURRENT_TIMESTAMP
 
This function returns the current date and time as a datetime data type. Same as GETDATE().
  1. select CURRENT_TIMESTAMP  
GETUTCDATE()
 
This function returns the current UTC (Universal Time Coordinate) time or Greenwich Mean Time.
  1. 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.
  1. select SYSDATETIMEOFFSET()  
SYSUTCDATETIME()
 
This function works the same as GETUTCDATE(), however, returns the newer datetime2 data type. 
  1. 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.
  1. 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.
  1. select TODATETIMEOFFSET(<data that resolves to datetime>, <time zone>)
DAY()
 
This function returns an integer representing the day part of the specified date.
  1. select DAY(<date>)  
MONTH()
 
This function returns an integer representing the month part of the specified date.
  1. select MONTH(<date>)  
YEAR()
 
This function returns an integer representing the year part of the specified date.
  1. select YEAR(<date>)  
ISDATE()
 
This function determines whether an input expression is a valid date.
  1. select ISDATE(<expression>)  
DATEADD()
 
This function adds an interval to a date and returns a new date.
  1. select DATEADD(<datepart>, <number>, <date>)  
DATEDIFF()
 
This function returns the difference between two specified dates in a specified unit of time.
  1. select DATEDIFF(<datepart>, <startdate>, <enddate>)
DATENAME()
 
This function returns a string representing the name of the specified datepart of the specified date.
  1. select DATENAME(<datepart>, <date>)  
DATEPART()
 
This function returns an integer that represents the specified datepart of the specified date.
  1. 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.