Date and Time Functions in SQL Server 2008

Higher-Precision System Date and Time Functions 

SYSDATETIME

Returns a datetime2(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 not included.
Example: 2011-07-26 23:48:50.2038641

SYSDATETIMEOFFSET

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.
Example: 2011-07-26 23:48:50.2038641 +02:00

SYSUTCDATETIME

Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. This function returns the Greenwich Mean Time (GMT) or the Coordinated Universal Time (UTC). This time is derived from the current local system time and the time zone settings of the server where SQL Server 2008 is running.
Example: 2011-07-26 21:48:50.2038641

Lower-Precision System Date and Time Functions

CURRENT_TIMESTAMP

Returns a datetime2(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 not included.
Example: 2011-07-26 23:53:53.417

GETDATE

Returns a datetime2(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 not included.
Example: 2011-07-26 23:54:46.913

GETUTCDATE

Returns a datetime2(7) 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).
Example: 2011-07-26 21:55:13.400

Functions That Get Date and Time Parts

DATENAME

Returns a character string that represents the specified datepart of the specified date.

DATEPART

Returns an integer that represents the specified datepart of the specified date.

DAY

Returns an integer that represents the day day part of the specified date.

MONTH

Returns an integer that represents the month part of a specified date.

YEAR

Returns an integer that represents the year part of a specified date.

Functions That Get Date and Time Difference

DATEDIFF

Returns the number of date or time datepart boundaries that are crossed between two specified dates.

 Functions That Modify Date and Time Values

DATEADD

Returns a new datetime value by adding an interval to the specified datepart of the specified date.

SWITCHOFFSET

SWITCHOFFSET changes the time zone offset of a DATETIMEOFFSET value and preserves the UTC value. This function returns a DATETIMEOFFSET value that is changed from the stored time zone offset to a specified new time zone offset. It is similar to the TODATETIMEOFFSET function, but instead of only changing the time zone value of a DATETIMEOFFSET data type, this function also changes the time as well. When you use this function the new date, time and time zone will be adjusted to reflect what time it would be in the new time zone based on the date/time being changed. This function is useful when displaying a date that matches a specific time zone even when the base date contains a date that represents a different time zone. This function will be great when trying to support an application that supports users in multiple time zones.

Example:

SELECT SYSDATETIMEOFFSET() AS [SYSDATETIMEOFFSET],
SWITCHOFFSET (SYSDATETIMEOFFSET(), ‘-00:00’) AS [SWITCHOFFSET];

SYSDATETIMEOFFSET                                     SWITCHOFFSET
——————————-                       ————————————-
2010-03-03 22:00:00.00 +05:30                    2010-03-03 16:30:00.2343750 +00:00

TODATETIMEOFFSET

TODATETIMEOFFSET transforms a datetime2 value into a datetimeoffset value. The datetime2 value is interpreted in local time for the specified time_zone.

SELECT TODATETIMEOFFSET (GETDATE(),’+05:30′) AS [TODATETIMEOFFSET];

TODATETIMEOFFSET
———————————
2010-03-03 22:00:00.000 +05:30

To use this function you pass two parameters. One parameter is a DATETIMEOFFSET data type value and the other is a time zone value. You might want to do this if you copy a database that stores the local time in a DATETIMEOFFSET column to another system that has another time zone and you want to make the date reflect the time zone of the new system.

Advertisements
This entry was posted in SQL and tagged , , , , , , , , , , , , , , , , , , . Bookmark the permalink.

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