SELECT Stored Dates in Any Time Zone in Estern Time?
448271Aug 9 2005 — edited Aug 10 2005I 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.