Useful Date Conversation in SQL Server 2010

The following Queries use the six SQL Server system functions that return current date and time to return the date, time or both. The values are returned in series; therefore, their fractional seconds might be different.

A. Getting the current system date and time

SELECT 'SYSDATETIME()      ', SYSDATETIME();

SELECT 'SYSDATETIMEOFFSET()', SYSDATETIMEOFFSET();

SELECT 'SYSUTCDATETIME()   ', SYSUTCDATETIME();

SELECT 'CURRENT_TIMESTAMP  ', CURRENT_TIMESTAMP;

SELECT 'GETDATE()          ', GETDATE();

SELECT 'GETUTCDATE()       ', GETUTCDATE();

/* Returned:

SYSDATETIME()            2007-05-03 18:34:11.9351421

SYSDATETIMEOFFSET()      2007-05-03 18:34:11.9351421 -07:00

SYSUTCDATETIME()         2007-05-04 01:34:11.9351421

CURRENT_TIMESTAMP        2007-05-03 18:34:11.933

GETDATE()                2007-05-03 18:34:11.933

GETUTCDATE()             2007-05-04 01:34:11.933

*/

B. Getting the current system date

SELECT 'SYSDATETIME()      ', CONVERT (date, SYSDATETIME());

SELECT 'SYSDATETIMEOFFSET()', CONVERT (date, SYSDATETIMEOFFSET());

SELECT 'SYSUTCDATETIME()   ', CONVERT (date, SYSUTCDATETIME());

SELECT 'CURRENT_TIMESTAMP  ', CONVERT (date, CURRENT_TIMESTAMP);

SELECT 'GETDATE()          ', CONVERT (date, GETDATE());

SELECT 'GETUTCDATE()       ', CONVERT (date, GETUTCDATE());

/* Returned:

SYSDATETIME()            2007-05-03

SYSDATETIMEOFFSET()      2007-05-03

SYSUTCDATETIME()         2007-05-04

CURRENT_TIMESTAMP        2007-05-03

GETDATE()                2007-05-03

GETUTCDATE()             2007-05-04

*/

C. Getting the current system time

SELECT 'SYSDATETIME()      ', CONVERT (time, SYSDATETIME());

SELECT 'SYSDATETIMEOFFSET()', CONVERT (time, SYSDATETIMEOFFSET());

SELECT 'SYSUTCDATETIME()   ', CONVERT (time, SYSUTCDATETIME());

SELECT 'CURRENT_TIMESTAMP  ', CONVERT (time, CURRENT_TIMESTAMP);

SELECT 'GETDATE()          ', CONVERT (time, GETDATE());

SELECT 'GETUTCDATE()       ', CONVERT (time, GETUTCDATE());

/* Returned

SYSDATETIME()            18:25:01.6958841

SYSDATETIMEOFFSET()      18:25:01.6958841

SYSUTCDATETIME()         01:25:01.6958841

CURRENT_TIMESTAMP        18:25:01.6930000

GETDATE()                18:25:01.6930000

GETUTCDATE()             01:25:01.6930000

*/