Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : SQL Server Management Studio Shortcuts
Built-in Functions
Home > Functions > SQL Server Date and Time Functions
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
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.

Syntax:

CURRENT_TIMESTAMP
DATEADD Returns a new datetime value based on adding an interval to the specified date.

Syntax:

DATEADD (datepart , number, date )
DATEDIFF Returns the number of date and time boundaries crossed between two specified dates.

Syntax:

DATEDIFF ( datepart , startdate , enddate )
DATENAME Returns a character string representing the specified datepart of the specified date.

Syntax:

DATENAME ( datepart ,date )
DATEPART Returns an integer that represents the specified datepart of the specified date.

Syntax:

DATEPART ( datepart , date )
DAY Returns an integer representing the day datepart of the specified date.

Syntax:

DAY ( date )
GETDATE Returns the current system date and time in the SQL Server standard internal format for datetime values.

Syntax:

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.

Syntax:

GETUTCDATE ( )
ISDATE Determines whether a datetime or smalldatetime input expression is a valid date or time value.

Syntax:

ISDATE ( expression )
MONTH Returns an integer that represents the month part of a specified date.

Syntax:

MONTH ( date )
YEAR Returns an integer that represents the year part of a specified date.

Syntax:

YEAR ( date )

New Date and Time Functions in SQL Server 2008

Function Description
SWITCHOFFSET SWITCHOFFSET changes the time zone offset of a DATETIMEOFFSET value and preserves the UTC value.

Syntax:

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.

Syntax:

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.

Syntax:

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.

Syntax:

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

Syntax:

SYSUTCDATETIME ( )

Related Articles :