Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SELECT Stored Dates in Any Time Zone in Estern Time?

448271Aug 9 2005 — edited Aug 10 2005
I need to write a SQL which will identify EST or EDT for the values in a date column. We are in CST time currently but this table can be CREATed in another time zone and get populated accordingly.

I have table, a_tab, and it has a date column, a_date_col, with following values inserted to it:

a_date_col ,
..
02/22/2003 05:30:10 PM
12/10/2003 10:11:05 AM
01/15/2004 01:30:56 PM
02/24/2004 03:48:42 AM
02/25/2004 11:48:42 AM
11/19/2004 12:11:05 AM
03/19/2005 09:30:33 PM
...
04/26/2003 06:31:50 AM
05/31/2003 07:33:25 PM
06/26/2004 07:46:01 PM
07/30/2004 11:13:13 AM
09/28/2004 08:55:22 PM
10/19/2004 06:33:55 PM
08/04/2005 11:12:06 PM

Here are some reference date results from running SQL:

SELECT TO_CHAR(CURRENT_DATE,'MM/DD/YYYY hh:mm:ss') current_date, DBTIMEZONE, SESSIONTIMEZONE, SYSTIMESTAMP, CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;

CURRENT_DATE DBTIMEZONE SESSIONTIMEZONE SYSTIMESTAMP CURRENT_TIMESTAMP LOCALTIMESTAMP
08/09/2005 02:16:24 PM -05:00 -05:00 8/9/2005 2:16:24.271929 PM -05:00 8/9/2005 2:16:24.271972 PM -05:00 8/9/2005 2:16:24.271972 PM

My requirement is to have a SQL to return the following values:

a_date_col ,
..
02/22/2003 06:30:10 PM EDT
12/10/2003 11:11:05 AM EDT
01/15/2004 02:30:56 PM EDT
02/24/2004 04:48:42 AM EDT
02/26/2004 12:48:42 AM EDT
11/19/2004 01:11:05 AM EDT
03/19/2005 10:30:33 PM EDT
...
04/26/2003 07:31:50 AM EST
05/31/2003 08:33:25 PM EST
06/26/2004 08:46:01 PM EST
07/30/2004 12:13:13 PM EST
09/28/2004 09:55:22 PM EST
10/19/2004 07:33:55 PM EST
08/05/2005 12:12:06 AM EST

I tried few options and combinations but were not very efficient and I wonder if this is possible or we have to write a PL/SQL function?

Any help will be greatly appreciated.
Thanks,
Adam Tadj.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 7 2005
Added on Aug 9 2005
4 comments
571 views