This post contains a list of Transact-SQL date and time functions available in SQL Server (as of SQL Server 2017).
This includes functions that return the date or parts of the date, as well as functions that manipulate and/or format the date.
System Date/Time Values
The following functions return the system date and time. The date/time values returned by these functions are all derived from the operating system that the instance of SQL Server runs on.
Higher precision
Lower precision
Return Date/Time Parts
These functions return the applicable part of the date that you specify, from the date that you specify.
For example, you can use MONTH() to return the month component of the given date.
Return a Date/Time Value from its Parts
These functions allow to you build a date and/or time value from the various parts that make up the date/time. Simply provide the various parts, and the function will return the date/time in the applicable data type.
For example, you can pass the year, month, and day values to the DATEFROMPARTS() function, and it will return a date value consisting of those parts.
- DATEFROMPARTS ( year, month, day )
- DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision)
- DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds)
- DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision)
- SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )
- TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )
Return the Difference Between Two Date/Time Values
The following functions can be used to return the difference between two different date/time values.
- DATEDIFF ( datepart , startdate , enddate )
- DATEDIFF_BIG ( datepart , startdate , enddate )
Note that these functions do essentially the same thing. The difference is in the return value.
Modify Date/Time Values
- DATEADD (datepart, number , date )
- EOMONTH ( start_date [, month_to_add ] )
- SWITCHOFFSET (DATETIMEOFFSET , time_zone)
- TODATETIMEOFFSET (expression , time_zone)
Set or Return Session Format Functions
- @@DATEFIRST
- SET DATEFIRST { number | @number_var }
- SET DATEFORMAT { format | @format_var }
- @@LANGUAGE
- SET LANGUAGE { [ N ] ‘language‘ | @language_var }
- sp_helplanguage [ [ @language = ] ‘language‘ ]
Validate a Date/Time Value
This function allows you to verify whether the argument passed to it has a valid date or time value.
- ISDATE ( expression)