I am decisive to write another short articles or tidbits on Transaction SQL date/time functions.
Most, if not all, modern day computer systems collect data which contains dates and times.
Not only is it important to know when a sales order came in, but we should fulfill the request using some type of queue (LIFO). If if was a stack (FIFO) implementation, the first person may never get his order!
Today, I will be exploring the DATEPART() function. This function has 15 different input parameters that can be passed to change the resulting output.
In short, the function takes a date part and date time variables as input returns the part that you want as an integer. I will be concentrating on how to extract the time zone offset (TZ) in this article.
The examples below are sample calls to the function. The inputs puts are valid date, null value and a empty string.
|
1 2 3 4 5 6 7 8 9 10 11 |
-- -- Functions to get date and time parts as INT -- -- Time Zone Offset SELECT SYSDATETIMEOFFSET() AS FULL_DATE4, DATEPART(TZ, SYSDATETIMEOFFSET())/60 AS TZ_OFFSET_INT, DATEPART(TZ, NULL) AS TZ_OFFSET_NULL, DATEPART(TZ, '') AS TZ_OFFSET_EMPTY GO |
The output from the above test is listed below. Please note that a empty string returns 0 minute offset from GMT or UTC.
|
1 2 3 4 5 |
output: FULL_DATE4 TZ_OFFSET_INT TZ_OFFSET_NULL TZ_OFFSET_EMPTY ---------------------------------- ------------- -------------- --------------- 2013-12-03 16:31:46.9757820 -05:00 -5 NULL 0 |
In as nutshell, pass the time zone parameter to extract the offset in minutes from the date time variable. The the example above, I divided by 60 minutes to convert to an offset in hours.
