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.

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'

Date and Time functions in SQL Server

 

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>)

DATEADD()

 

This function adds an interval to a date and returns a new date.

select DATEADD(<datepart>, <number>, <date>)

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.