SQL Server has strong date and time support.
It offers 6 different datetime data types for storage and processing.
As well as many datetime, date, time, and datetimeoffset functions.
This function returns the current datetime on the database server.
SELECT GETDATE() AS 'Now'
| Now |
|---|
| 2023-12-21 16:23:47.0518282 |
In SQL Server, the standard output format for dates is yyyy-mm-dd.
These are the built-in date and time functions in SQL Server.
Click on a function for more details.
| Function | Description |
|---|---|
| CURRENT_TIMESTAMP | Returns the current datetime on the database server |
| CURRENT_TIMEZONE | Returns name of the timezone observed by the database server |
| CURRENT_TIMEZONE_ID | Returns the ID of the timezone observed by the database server |
| DATEADD | Modifies a date by adding an integer datepart value |
| DATEDIFF | Returns an integer representing the difference (in dateparts) between two dates |
| DATEDIFF_BIG | Returns a big integer representing the difference (in dateparts) between two dates |
| DATEFROMPARTS | Returns a date value for the specified year, month, and day values |
| DATENAME | Returns a name representing the specified datepart of a given date |
| DATEPART | Returns an integer representing the specified datepart of a date |
| DATETIME2FROMPARTS | Returns a datetime2 value for the specified date and time parameters |
| DATETIMEFROMPARTS | Returns a datetime value for the specified date and time parameters |
| DAY | Returns integer representing the day of a given date |
| EOMONTH | Returns the last day of the month for a given date |
| GETDATE | Returns the current datetime on the database server |
| GETUTCDATE | Returns current datetime as UTC time on the database server |
| ISDATE | Returns 1 if the parameter is a valid date, time, or datetime value; otherwise, 0 |
| MONTH | Returns an integer representing the month of a given date |
| SMALLDATETIMEFROMPARTS | Returns a smalldatetime value for the specified date and time parameters |
| SWITCHOFFSET | Returns a datetimeoffset value that is switched to a new time zone offet |
| SYSDATETIME | Returns a datetime2 value that contains the date and time on the database server |
| SYSDATETIMEOFFSET | Returns datetimeoffset value for the datetime and offet on the database server |
| SYSUTCDATETIME | Returns the UTC datetime2 value for the date and time on the database server |
| TIMEFROMPARTS | Returns a time value for the specified time data |
| TODATETIMEOFFSET | Returns a datetimeoffset value for a given datetime2 expression |
| YEAR | Returns an integer representing the year of a given date |
As a reminder, SQL Server supports these date and time data types.
| Data type | Format | Range |
|---|---|---|
| time | hh:mm:ss[.nnnnnnn] | 00:00:00.0000000 through 23:59:59.9999999 |
| date | YYYY-MM-DD | 0001-01-01 through 9999-12-31 |
| smalldatetime | YYYY-MM-DD hh:mm:ss | 1900-01-01 through 2079-06-06 |
| datetime | YYYY-MM-DD hh:mm:ss[.nnn] | 1753-01-01 through 9999-12-31 |
| datetime2 | YYYY-MM-DD hh:mm:ss[.nnnnnnn] | 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 |
| datetimeoffset | YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm | 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 (in UTC) |
For more information on data types see our SQL Data Types Reference.
