SQL Server Helper - Date and Time Functions

Tip of the Day : SQL Server Database Design - Twitter Profile and Followers

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