SQL Pocket Guide, 3rd Edition

Datetime Functions: SQL Server

SQL Server 2008 introduces a set of high-precision functions to return current datetime information:

SYSDATETIME()

Returns date and time as a DATETIME2 value.

SYSDATETIMEOFFSET()

Returns date, time, and time zone offset as a DATETIMEOFFSET value.

SYSUTCDATETIME

Returns current UTC time as a DATETIME2 value.

SQL Server continues to support the following functions from previous releases:

CURRENT_TIMESTAMP or GETDATE()

Returns the current date and time on the server as a datetime value.

GETUTCDATE()

Returns the current UTC date and time, as derived from the server’s time and time zone setting.

SQL Server implements two functions for date arithmetic:

DATEADD(datepart, interval,date)

Adds interval (expressed as an integer) to date. Specify a negative interval to perform subtraction. The datepart argument is a keyword specifying the portion of the date to increment, and it may be any of the following: year, yy, yyyy, quarter, qq, q, month, mm, m, dayofyear, dy, y, day, dd, d, week, wk, ww, hour, hh, minute, mi, n, second, ss, s, millisecond, ms. For example, to add one day to the current date, use DATEADD(day, 1, GETDATE()).

DATEDIFF(datepart , startdate , enddate)

Returns enddate – startdate expressed in terms of the units you specify for the datepart argument. For example, to compute the number of minutes between the current time and UTC time, use DATEDIFF(mi, GETUTCDATE(), GETDATE()).

SQL Server 2008 introduces new functions to work with time zone offsets:

SWITCHOFFSET( ...