GMT-PST Converstions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

GMT-PST Converstions

Author

Topic

rds207
Posting Yak Master

198 Posts

Posted - 2010-02-06 : 23:47:27

I have a datetime field which is in GMT ,
1.i need to convert it into PST
2.Get the Date
3.write a case statement to adjust it to day light savings

---------My insert Statement ---------------------------------
Insert INTO DW_T_ASW_JOBDATA
(
[FINISH_DAY] ,
[FINISH_TIME] ,
[FINISH_TIME_GMT] )

SELECT
dateadd(d, (datediff(d, 0,dateadd(HOUR,-8,EC_FINISH_TIME))),0),
dateadd(HOUR,-8,EC_FINISH_TIME),
EC_FINISH_TIME
FROM dbo.DW_T_EC_SBA )

i am able to get the values in standard time, thats by subtracting 8 , but on day light savings i need to subtract by 7 hrs ,

so i need to ,

write CASE statements for the FINISH_TIME , FINISH_DAY fields while converting then to PST , which is in GMT , and when converted if the PST time falls in STANDARD timing then - 8 or if the PST falls in DAY LIGHT SAVING - 7

i am new to USA , so bit confused with this DAY LIGHT SAVINGS , as in my country the time is same all year around :-)

i am sure people might have done this before , is their any function
which can diractly be used to convert the day light saving and standard timngs ...

Please Help.....

Thank you

Kristen
Test

22859 Posts

Posted - 2010-02-07 : 02:16:45

Use datetimeoffset - that will get you the time zone offset, but not the daylight saving

~~Please do not type your messages in all capital letters.~~

rds207
Posting Yak Master

198 Posts

Posted - 2010-02-07 : 02:31:33

So , do think i need have another feild with datetimeoffset?

then how about day light savings...

am sorry for having all caps in my message, just changed it ...

quote:
Originally posted by Kristen

Use datetimeoffset - that will get you the time zone offset, but not the daylight saving

Please do not type your messages in all capital letters.

Kristen
Test

22859 Posts

Posted - 2010-02-07 : 02:47:47

Not necessarily, you could just use it in the "presentation" part, or to do the adjustment calculations.

I only mentioned it because Microsoft have added it to assist with this type of problem, and hopefully it makes the job easier! Worth having a read in the SQL Documentation.

rds207
Posting Yak Master

198 Posts

Posted - 2010-02-07 : 15:05:17

could any body please let me know how i can dateoffset in my below query , to adjust standard and daylight savings , i need to convert GMT to PST..

Please help ...:-(

quote:
Originally posted by rds207

I have a datetime field which is in GMT ,
1.i need to convert it into PST
2.Get the Date
3.write a case statement to adjust it to day light savings

---------My insert Statement ---------------------------------
Insert INTO DW_T_ASW_JOBDATA
(
[FINISH_DAY] ,
[FINISH_TIME] ,
[FINISH_TIME_GMT] )

SELECT
dateadd(d, (datediff(d, 0,dateadd(HOUR,-8,EC_FINISH_TIME))),0),
dateadd(HOUR,-8,EC_FINISH_TIME),
EC_FINISH_TIME
FROM dbo.DW_T_EC_SBA )

i am able to get the values in standard time, thats by subtracting 8 , but on day light savings i need to subtract by 7 hrs ,

so i need to ,

write CASE statements for the FINISH_TIME , FINISH_DAY fields while converting then to PST , which is in GMT , and when converted if the PST time falls in STANDARD timing then - 8 or if the PST falls in DAY LIGHT SAVING - 7

i am new to USA , so bit confused with this DAY LIGHT SAVINGS , as in my country the time is same all year around :-)

i am sure people might have done this before , is their any function
which can diractly be used to convert the day light saving and standard timngs ...

Please Help.....

Thank you

rds207
Posting Yak Master

198 Posts

Posted - 2010-02-08 : 13:33:58

I am thinking to write the case statements like if the start_time between date '14-mar-2010' and '7-nov-2010'then dateadd(HOUR,-7,EC_FINISH_TIME) else dateadd(HOUR,-8,EC_FINISH_TIME)

Is there any better option than this?

Please help

Kristen
Test

22859 Posts

Posted - 2010-02-08 : 13:48:51

I think you probably need a table of Start/Finish dates for each year.

The rules (from memory) are something sensible like "Second Sunday of March" / "First Sunday of November" but then occasionally the politicians get in the way and change it to try to synchronise USA and Europe, or somesuch.

I remember a few years ago Microsoft had to issue a Windows patch to fix Daylight saving time because someone had decided to override the rules!

You should probably include country code, or time-zone, in your table.

Maybe also the time at which it happens - I think that is the trickiest part when the clocks go back: at 3am the time goes back to 2am (which is what it was an hour ago; how do you know if it is 02:01 for the first time, or the second time?)

Might be important to set your server to IGNORE Daylight Saving Time so that the SERVER time does not change ...

... and make sure that you server keeps time synchronised frequently - if you server gets a time adjustment that is more than a few ms that's going to confuse everything else!

rds207
Posting Yak Master

198 Posts

Posted - 2010-02-08 : 14:10:13

Yeah Kristen ,

I have a code which is in oracle using new_time()

oracle code --------------------------------------------------------

CASE
WHEN new_time(wij.queue_time,'gmt','pdt') between '11-mar-2007' and '4-nov-2007'
or new_time(wij.queue_time,'gmt','pdt') between '9-mar-2008' and '2-nov-2008'
or new_time(wij.queue_time,'gmt','pdt') between '8-mar-2009' and '1-nov-2009'
or new_time(wij.queue_time,'gmt','pdt') between '14-mar-2010' and '7-nov-2010'
THEN new_time(wij.queue_time,'gmt','pdt')
ELSE new_time(wij.queue_time,'gmt','pst')
END as QUEUE_TIME,

I tried to google around and found that datetimeoffset() is the function used in sql server 2008 as new_time()in oracle ...

Could you please let mw know how i can translate for the above values using datetimeoffset?

Kristen
Test

22859 Posts

Posted - 2010-02-08 : 14:29:24

Sorry, I have no familiarity with Oracle / new_time()

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-02-08 : 15:40:56

SQL has several date functions. Perhaps the GetUtcDate() function might help?

SELECT DATEDIFF(HOUR, GETUTCDATE(), GETDATE()) AS UtcOffset

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources