Tuesday, February 4, 2014

SQL Server: Different ways of getting current date and time

What is the usual way of getting current date and time from SQL Server? Obviously, the answer is GETDATE function. Do you know that there are 7 different ways of getting the same with bit differences? Here are the ways and their differences;

Function Explanation
GETDATE() Returns datetime.
GETUTCDATE() Returns datetime in Universal Time Coordinated.
CURRENT_TIMESTAMP Returns datetime. This is ANSI Standard function
SYSDATETIME() Returns datetime2.
SYSUTCDATETIME() Returns datetime2 in Universal Time Coordinated.
SYSDATETIMEOFFSET() Returns datetimeoffset (time zone offset is included)
ODBC Canonical functions Not a standard way but they can be used too. There are many functions and NOW() is similar to GETDATE() which returns datetime. This has to be called as;
{fn NOW()}

  1. SELECT GETDATE() [GETDATE]
  2. SELECT GETUTCDATE() [GETUTCDATE]
  3. SELECT CURRENT_TIMESTAMP [CURRENT_TIMESTAMP]
  4. SELECT SYSDATETIME() [SYSDATETIME]
  5. SELECT SYSUTCDATETIME() [SYSUTCDATETIME]
  6. SELECT SYSDATETIMEOFFSET() [SYSDATETIMEOFFSET]
  7. SELECT {fn NOW()} [NOW]

image

No comments: