Convert Gregorian Date to timestamp in Oracle

angel7170
angel7170 used Ask the Experts™
on
Hello,

I am trying to convert a Gregorian date format to regular timestamp in Oracle SQL. Also, I need that time to account for Daylight savings time. Here is syntax I used but using NEW_TIME cuts the milleseconds from the timestamp. How can I account for millesonds and still do the DST. Please assist. Thank you

Actual Column (Assign Time) : 2015-05-27T17:07:12.065Z
Expected Result: 5/27/2015 12:07:12.065000000 PM
Received outcome using New_time: 5/27/2015 12:07:12 PM


NEW_TIME (
             TO_TIMESTAMP (z."PXASSIGNDATETIME",
                           'yyyy-mm-dd"T"hh24:mi:ss.FF"Z"'),
             'GMT',
             'EDT'),
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012
Commented:
If you want to get 12:07 pm  as a timestamp

FROM_TZ(TO_TIMESTAMP(z."PXASSIGNDATETIME", 'yyyy-mm-dd"T"hh24:mi:ss.FF"Z"'), 'GMT') AT TIME ZONE '-05:00'

but...  EDT (assuming you mean US/Eastern DST) would actually be 13:07, not 12:07  because EDT has a 4 hour offset, not 5.

easiest way to handle that is to use the full tz name:

FROM_TZ(TO_TIMESTAMP(z."PXASSIGNDATETIME", 'yyyy-mm-dd"T"hh24:mi:ss.FF"Z"'), 'GMT') AT TIME ZONE 'US/Eastern'


both of these return timestamp with time zone types
so,in  either case , to get the formatting you want,  wrap the whole thing in to_char


to_char(FROM_TZ(TO_TIMESTAMP(z."PXASSIGNDATETIME", 'yyyy-mm-dd"T"hh24:mi:ss.FF"Z"'), 'GMT') at time zone 'US/Eastern','fmmm/dd/yyyy hh24:mi:ss.ff9 am')

Author

Commented:
Awesome, it works! I used

FROM_TZ(TO_TIMESTAMP(z."PXASSIGNDATETIME", 'yyyy-mm-dd"T"hh24:mi:ss.FF"Z"'), 'GMT') AT TIME ZONE 'US/Eastern'

Thank you very much
Most Valuable Expert 2011
Top Expert 2012

Commented:
glad I could help.

the reason NEW_TIME didn't work is NEW_TIME only works on dates, not timestamps.

So, when you passed in a timestamp, it converted it implicitly to a date and then returned a date, hence the missing milliseconds.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial