Convert Gregorian Date to timestamp in Oracle

angel7170 used Ask the Experts™

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

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012
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')


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

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