DATETIME Functions

Posted: November 21, 2011 in SQL Server 2008

The following DATETIME functions are available:

-- Returns the current database system timestamp as a DATETIME value without the database time zone offset
-- This value is derived from the operating system of the computer on which the instance of SQL Server is running
SELECT GETDATE();

-- Returns a DATETIME2(7) value that contains the date and time of the computer on which the instance of SQL Server is running
SELECT SYSDATETIME();

-- 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
SELECT SYSDATETIMEOFFSET();

-- Returns a DATETIME2 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)
-- The fractional second precision specification has a range from 1 to 7 digits. The default precision is 7 digits
SELECT SYSUTCDATETIME();

-- Returns a DATETIMEOFFSET value that is changed from the stored time zone offset to a specified new time zone offset
SELECT SWITCHOFFSET('1998-09-20 7:45:50.71345 -5:00',  '-08:00');

-- Returns a DATETIMEOFFSET value that is translated from a DATETIME2 expression
DECLARE @today DATETIME2;
SELECT @today = GETDATE();
SELECT TODATETIMEOFFSET(@today, '-07:00');

-- Returns the current database system timestamp as a DATETIME value without the database time zone offset
-- This value is derived from the operating system of the computer on which the instance of SQL Server is running
SELECT CURRENT_TIMESTAMP;

-- Returns an integer that represents the year of the specified date
SELECT YEAR(GETDATE());

-- Returns an integer that represents the month of the specified date
SELECT MONTH(GETDATE());

-- Returns an integer representing the day (day of the month) of the specified date
SELECT DAY(GETDATE());

-- Returns the current database system timestamp as a DATETIME value
-- The database time zone offset is not included. This value represents the current UTC time (Coordinated Universal Time)
-- This value is derived from the operating system of the computer on which the instance of SQL Server is running
SELECT GETUTCDATE();
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s