Home » SQL & PL/SQL » SQL & PL/SQL » GMT to EST or EDT (ORACLE 11G)
GMT to EST or EDT [message #430005] Fri, 06 November 2009 15:21 Go to next message
smandeh
Messages: 3
Registered: November 2009
Location: United States
Junior Member
Hello all,

Maybe someone can help me with datetime conversion.

I have an incoming datetime, date time 04/12/2008 9:30:00. This time is in GMT but I need to convert it to EST prior to storing it in the database. I sm currently using new_time function to do this and it works fine but with the time change in Nov I am off by one hour.

I need to write a SQL statement that converts the incoming datetime from GMT to EST when DST is not being observed or EDT when DST is being observed.

I am a PL/SQL newbie. Pleae forgive me if this is a trivial matter.

Thanks for your help and time.
Big Bird
Re: GMT to EST or EDT [message #430028 is a reply to message #430005] Sat, 07 November 2009 01:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I need to write a SQL statement that converts the incoming datetime from GMT to EST when DST is not being observed or EDT when DST is being observed.

Post examples of what you mean with this.

Regards
Michel
Re: GMT to EST or EDT [message #430045 is a reply to message #430028] Sat, 07 November 2009 08:00 Go to previous messageGo to next message
smandeh
Messages: 3
Registered: November 2009
Location: United States
Junior Member

Michel,

Currently I have the below SQL:

SELECT NEW_TIME('EVENT_DATE', 'GMT', 'EST') INTO CONVERTED_DATE FROM DUAL;

I then insert CONVERTED_DATE into my Database. The EVENT_DATE is the incoming date. This works fine but manually need to change during DST to the below code

SELECT NEW_TIME('EVENT_DATE', 'GMT', 'EDT') INTO CONVERTED_DATE FROM DUAL;

I need one SQL statement that will do the proper conversion regardless whether or not it is Day Light Saving time.

Thanks
Big Bird
Re: GMT to EST or EDT [message #430048 is a reply to message #430045] Sat, 07 November 2009 09:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The answer is in the following post:
http://www.orafaq.com/forum/m/328620/102589/?#msg_328620

Regards
Michel
Re: GMT to EST or EDT [message #431051 is a reply to message #430048] Fri, 13 November 2009 18:12 Go to previous message
smandeh
Messages: 3
Registered: November 2009
Location: United States
Junior Member
Michel,

Thank you so much for your help. I appreciate it. I was able to resolve the issue.

Big Bird.
Previous Topic: Minus in Select
Next Topic: 'ytd'||To_char(sysdate,'yyyy') in alias
Goto Forum:
  


Current Time: Sun Dec 11 02:19:09 CST 2016

Total time taken to generate the page: 0.09494 seconds