SQL Date Functions

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.

Example

#

This function returns the current datetime on the database server.

SELECT GETDATE() AS 'Now'
Result:  1 record
Now
2021-12-02 13:05:00.2401979

In SQL Server, the standard output format for dates is yyyy-mm-dd.


Date and Time Functions

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

Date and Time Data Types

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.


You may also like



Guides


vsn 3.1