This is the last short articles or tidbits on Transaction SQL date/time functions.
It has surely been a-lot of work to write 41 articles on every way to slice and dice dates, times and offsets.
Today, I am going to talk about the SWTICHOFFSET() function. This function is useful when you want to convert time from one zone to another.
The function takes a date/time offset variable as input as well as the new offset value. It returns a date/time offset variable in the new zone.
The examples below convert the variable to one zone ahead three hours and one zone behind three hours.
1 2 3 4 5 6 7 8 9 |
/* Sample use of function */ -- Adjust two date time offset variables SELECT SWITCHOFFSET ('1998-09-20 7:45:50.71345 -5:00', '-08:00') as SUB3HRS, SWITCHOFFSET ('1998-09-20 7:45:50.71345 -5:00', '-02:00') as ADD3HRS GO |
The output from the test above is listed below.
1 2 3 4 5 |
output: SUB3HRS ADD3HRS ---------------------------------- ---------------------------------- 1998-09-20 04:45:50.7134500 -08:00 1998-09-20 10:45:50.7134500 -02:00 |
In summary, the SWITCHOFFSET() function is useful when you have servers in different time zones and you want to know when something happened in relationship to your zone.