Sql server® 2012 t-sql fundamentals

date and Time Functions

In this section, I describe functions that operate on date and time data types, including GETDATE, 

CURRENT_TIMESTAMP, GETUTCDATE, SYSDATETIME, SYSUTCDATETIME, SYSDATETIMEOFFSET, CAST, 

CONVERT, SWITCHOFFSET, TODATETIMEOFFSET, DATEADD, DATEDIFF, DATEPART, YEAR, MONTH, 

DAY, DATENAME, various FROMPARTS functions, and EOMONTH. 

The functions SYSDATETIME, SYSUTCDATETIME, SYSDATETIMEOFFSET, SWITCHOFFSET, and 

TODATETIMEOFFSET were introduced in SQL Server 2008. Existing functions were enhanced 

to sup port the newer types and parts. The various FROMPARTS functions and the EOMONTH 

function were introduced in SQL Server 2012. 

Current date and Time

The following niladic (parameterless) functions return the current date and time values in the system 

where the SQL Server instance resides: GETDATE, CURRENT_TIMESTAMP, GETUTCDATE, SYSDATETIME, 

SYSUTCDATETIME, and SYSDATETIMEOFFSET. Table 2-3 provides the description of these functions.

TABLE 2-3

  Functions Returning Current Date and Time

Function

Return Type

Description

GETDATE

DATETIME

Current date and time

CURRENT_TIMESTAMP

DATETIME

Same as GETDATE but ANSI SQL–compliant

GETUTCDATE

DATETIME

Current date and time in UTC

SYSDATETIME

DATETIME2

Current date and time

SYSUTCDATETIME

DATETIME2

Current date and time in UTC

SYSDATETIMEOFFSET

DATETIMEOFFSET

Current date time including time zone

Note that you need to specify empty parentheses with all functions that should be specified with out 

parentheses, except the ANSI function CURRENT_TIMESTAMP. Also, because CURRENT_TIMESTAMP 

and GETDATE return the same thing but only the former is standard, it is recommended that you use 

the former. This is a practice that I try to follow in general—when I have several options that do the 

same thing with no functional or performance difference, and one is standard but others aren’t, my 

preference is to use the standard option.

The following code demonstrates using the current date and time functions.

SELECT 

  GETDATE()           AS [GETDATE], 

  CURRENT_TIMESTAMP   AS [CURRENT_TIMESTAMP], 

  GETUTCDATE()        AS [GETUTCDATE], 

  SYSDATETIME()       AS [SYSDATETIME], 

  SYSUTCDATETIME()    AS [SYSUTCDATETIME], 

  SYSDATETIMEOFFSET() AS [SYSDATETIMEOFFSET];

www.it-ebooks.info

---