August 31, 2010 at 3:54 pm
Our nationwide call center needs to determine if they are making a call between 8 a.m. and 8 p.m. in the local time zone of the call recipient.
I am going to use getutcdate in my calculation, but I'm not sure if/how the data is different between standard time and daylight savings time.
Example
Today is during daylight savings time
GETDATE() returns 2010-08-31 16:43:51.243
GETUTCDATE() returns 2010-08-31 21:43:51.250
So, if today is now during standard time
GETDATE() returns 2010-12-15 16:43:51.243
What would GETUTCDATE() return?
I know that I have to be careful because not all places follow daylight time, and we have a table for that. I just am not sure if UTCdate and date will stay 5 hours apart both in and out of DST.
August 31, 2010 at 8:22 pm
Well, you might want to look at the GetUTCDate and SysDateTimeOffset functions.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 31, 2010 at 8:25 pm
This query should help you see the differences:
SELECT SYSDATETIME() AS SYSDATETIME
,SYSDATETIMEOFFSET() AS SYSDATETIMEOFFSET
,SYSUTCDATETIME() AS SYSUTCDATETIME
,CURRENT_TIMESTAMP AS [CURRENT_TIMESTAMP]
,GETDATE() AS GETDATE
,GETUTCDATE() AS GETUTCDATE;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 31, 2010 at 9:12 pm
WayneS (8/31/2010)
This query should help you see the differences:
SELECT SYSDATETIME() AS SYSDATETIME,SYSDATETIMEOFFSET() AS SYSDATETIMEOFFSET
,SYSUTCDATETIME() AS SYSUTCDATETIME
,CURRENT_TIMESTAMP AS [CURRENT_TIMESTAMP]
,GETDATE() AS GETDATE
,GETUTCDATE() AS GETUTCDATE;
Again , my dear Wayne, you missed that the question was from SQL 2005 and we dont have those bold functions in it :-D.. Now i seriously feel you and your avatar match :hehe:...
No hard feelings mate, just wanted to pull your leg 😛
September 1, 2010 at 7:35 am
Thanks for the responding, but maybe I need to make my question clearer.
We use SQL2005.
We are in the central time zone.
We observe daylight savings time.
We need to make calls to Phoenix, AZ and Denver, CO between 8 a.m. and 8 p.m. of their local time.
Both cities are in the mountain time zone.
Denver observes daylight savings time. Phoenix does not.
We already have an Area Code table that tells us the AC's time zone and whether or not daylight savings time is observed for that AC.
What is the best way to calculate if our calls are being made to Phoenix and Denver between 8 a.m. and 8 p.m. local time?
Example:
Our LOCAL time in the SUMMER is 9:30 a.m.
DENVER's local time would be 8:30 a.m., and it would be okay to call.
PHOENIX' local time would be 7:30 a.m., and it would NOT be okay to call.
Our LOCAL time in the WINTER is 9:30 a.m.
DENVER's local time would be 8:30 a.m., and it would be okay to call.
PHOENIX' local time would be 8:30 a.m., and it would be okay to call.
I can put together a little lookup table that has contains the dates for the next 20 years of when we shift to and from daylight savings time. I can compare today's date with the lookup table to see if today lands in or out of daylight savings time. I'm a little gun shy to do so, just because the DST observation dates were modified in 2007. On the off-chance they monkey with that again, I'm afraid we'll forget to modify the table. I was hoping that the system had a way to determine whether we're observing daylight or standard time.
September 1, 2010 at 9:32 am
ColdCoffee (8/31/2010)
WayneS (8/31/2010)
This query should help you see the differences:
SELECT SYSDATETIME() AS SYSDATETIME,SYSDATETIMEOFFSET() AS SYSDATETIMEOFFSET
,SYSUTCDATETIME() AS SYSUTCDATETIME
,CURRENT_TIMESTAMP AS [CURRENT_TIMESTAMP]
,GETDATE() AS GETDATE
,GETUTCDATE() AS GETUTCDATE;
Again , my dear Wayne, you missed that the question was from SQL 2005 and we dont have those bold functions in it :-D.. Now i seriously feel you and your avatar match :hehe:...
No hard feelings mate, just wanted to pull your leg 😛
:blush: Whoops, I definitely missed that!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 1, 2010 at 9:45 am
Marcia Q (9/1/2010)
Thanks for the responding, but maybe I need to make my question clearer.We use SQL2005.
We are in the central time zone.
We observe daylight savings time.
We need to make calls to Phoenix, AZ and Denver, CO between 8 a.m. and 8 p.m. of their local time.
Both cities are in the mountain time zone.
Denver observes daylight savings time. Phoenix does not.
We already have an Area Code table that tells us the AC's time zone and whether or not daylight savings time is observed for that AC.
What is the best way to calculate if our calls are being made to Phoenix and Denver between 8 a.m. and 8 p.m. local time?
Example:
Our LOCAL time in the SUMMER is 9:30 a.m.
DENVER's local time would be 8:30 a.m., and it would be okay to call.
PHOENIX' local time would be 7:30 a.m., and it would NOT be okay to call.
Our LOCAL time in the WINTER is 9:30 a.m.
DENVER's local time would be 8:30 a.m., and it would be okay to call.
PHOENIX' local time would be 8:30 a.m., and it would be okay to call.
I can put together a little lookup table that has contains the dates for the next 20 years of when we shift to and from daylight savings time. I can compare today's date with the lookup table to see if today lands in or out of daylight savings time. I'm a little gun shy to do so, just because the DST observation dates were modified in 2007. On the off-chance they monkey with that again, I'm afraid we'll forget to modify the table. I was hoping that the system had a way to determine whether we're observing daylight or standard time.
I don't think you need a lookup table.
If you were to compare the date/time of a place known to observe DST (for example, your call center) to UTC date/time, you can tell if you're in DST or not by the offset.
You can then use that to calculate the time of the area code that you're calling, since you know whether it observes DST or not.
For example, for me (EST, observing DST):
DECLARE @IsDST BIT;
SET @IsDST = CASE WHEN DateDiff(hour, GetDate(), GetUTCDate()) = 4 THEN 'True'
ELSE 'False' END;
SELECT GETDATE() AS GETDATE,
GETUTCDATE() AS GETUTCDATE,
@IsDST;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 1, 2010 at 10:03 am
FROM 2005 BOL: The current UTC time is derived from the current local time and the time zone setting in the operating system
"is derived from the current local time and time zone setting"
There's no mention of DST adjustment. Only time zone adjustment.
The way I interpret it: Since it's derived from our local time (which will bump by an hour on November 7), the UTC will also bump by an hour on November 7.
So I would think that your calculation will still return 4 on November 7, 8, 9, etc.
Am I misunderstanding the BOL explanation? Or is BOL not accurate? Wouldn't be the first time in either case! 🙂
September 1, 2010 at 12:30 pm
Well, I'm in EST, which is GMT - 05:00. However, right now I'm showing a 4 hr difference, and we are in DST. So, since this is off of the system time, and in the system Date and Time control panel applet you control your time zone and DST, then I would have to conclude that it automatically adjusts for DST.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 1, 2010 at 12:37 pm
That's great. Your calc will do exactly what I need then!
And just because I'm paranoid, I have marked on my calendar to double check this on November 7th. 🙂
Thanks for the help, Wayne!
September 1, 2010 at 7:02 pm
Marcia Q (9/1/2010)
And just because I'm paranoid, I have marked on my calendar to double check this on November 7th. 🙂
I would not call this paranoid, but prudent. Always remember: CYA!
Thanks for the help, Wayne!
Not a problem. Sorry about the false start giving you SQL 2008 advice.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 6, 2016 at 6:54 am
Hello,
If I use below function to get the offset between UTC and localtime and add that in my transaction datetime column. Will it take care of Day Light saving time? Currently it is UTC -3 and from Last week of Oct it will be -2.
DATEDIFF(HH,SYSUTCDATETIME(), SYSDATETIME())
September 7, 2016 at 3:16 am
Marcia Q (9/1/2010)
The way I interpret it: Since it's derived from our local time (which will bump by an hour on November 7), the UTC will also bump by an hour on November 7.
AFAIK all countries which use DST except for the US will change the clocks on the last Sunday in April and October not 7th Nov
September 8, 2016 at 5:27 am
Marcia Q (8/31/2010)
Our nationwide call center needs to determine if they are making a call between 8 a.m. and 8 p.m. in the local time zone of the call recipient.
Once you resolve the SQL, you may ask yourself, "How do I know the correct time zone of the call recipient?" These days of cell phones, a phone with a given area code may not be physically located in that area code. And I'm not talking just about traveling. My sister moved 2 time zones west, then one zone east, and kept the same phone number and area code the entire time.
If this is a regulatory requirement, these kind of issues need to be addressed. We struggled with it at my last job.
September 14, 2016 at 12:28 pm
I am in the pacific time zone. Currently , according the time zones of the world , I am eight time zones away from Greenwhich time. My area implements day light savings , which we are in currently. So last spring, we "springed forward." That is, we set the clocks ahead by one hour.
At the moment if I run
SELECT
DATEDIFF(HH, GETDATE(), GETUTCDATE())
I get 7. After we go back to standard time later in the year , I am sure this number will revert to eight.
----------------------------------------------------
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply