Date and Time Functions in SQL Server 2008



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


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.


Up Next
    Ebook Download
    View all
    Learn
    View all