SQL Server Date and Time Functions¶
SQL Server date and time functions are scalar functions that perform an operation on a date and time input value and returns either a string, numeric, or date and time value. Some date and time functions are categorized as deterministic functions while others are non-deterministic.
The DATEADD, DATEDIFF, DAY, MONTH and YEAR functions are deterministic functions while the DATENAME, GETDATE and GETUTCDATE functions are non-deterministic functions. As for the DATEPART function, it is deterministic except when used as DATEPART(dw, date). The weekday datepart, dw, depends on the value set by SET DATEFIRST, which sets the first day of the week.
| Function | Description | Syntax |
|---|---|---|
| CURRENT_TIMESTAMP | Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is not included. | CURRENT_TIMESTAMP |
| DATEADD | Returns a new datetime value based on adding an interval to the specified date. | DATEADD (datepart , number, date ) |
| DATEDIFF | Returns the number of date and time boundaries crossed between two specified dates. | DATEDIFF ( datepart , startdate , enddate ) |
| DATENAME | Returns a character string representing the specified datepart of the specified date. | DATENAME ( datepart ,date ) |
| DATEPART | Returns an integer that represents the specified datepart of the specified date. | DATEPART ( datepart , date ) |
| DAY | Returns an integer representing the day datepart of the specified date. | DAY ( date ) |
| GETDATE | Returns the current system date and time in the SQL Server standard internal format for datetime values. | GETDATE ( ) |
| GETUTCDATE | Returns the datetime value representing the current UTC time (Coordinated Universal Time or Greenwich Mean Time). The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer on which the instance of Microsoft SQL Server is running. | GETUTCDATE ( ) |
| ISDATE | Determines whether a datetime or smalldatetime input expression is a valid date or time value. | ISDATE ( expression ) |
| MONTH | Returns an integer that represents the month part of a specified date. | MONTH ( date ) |
| YEAR | Returns an integer that represents the year part of a specified date. | YEAR ( date ) |
New Date and Time Functions in SQL Server 2008¶
| Function | Description | Syntax |
|---|---|---|
| SWITCHOFFSET | SWITCHOFFSET changes the time zone offset of a DATETIMEOFFSET value and preserves the UTC value. | SWITCHOFFSET ( DATETIMEOFFSET, time_zone ) |
| TODATETIMEOFFSET | TODATETIMEOFFSET transforms a datetime2 value into a datetimeoffset value. The datetime2 value is interpreted in local time for the specified time_zone. | TODATETIMEOFFSET ( expression, time_zone ) |
| SYSDATETIME | Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is not included. | SYSDATETIME ( ) |
| SYSDATETIMEOFFSET | Returns a datetimeoffset(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is included. | SYSDATETIMEOFFSET ( ) |
| SYSUTCDATETIME | Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The date and time is returned as UTC time (Coordinated Universal Time). | SYSUTCDATETIME ( ) |