Convert Gregorian Date to timestamp in Oracle
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.F
'GMT',
'EDT'),
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.F
'GMT',
'EDT'),
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
FROM_TZ(TO_TIMESTAMP(z."PX
Thank you very much