I am using ADS version 18.0.18-5 with an Oracle database version 184.108.40.206.0
I am trying to convert a Timestamp field to a Date formatted as MM/DD/YYYY without the Time portion. I have tried the following.
Field name is ADDED_TS
SUBSTR(ADDED_TS, 1, 9) —————————————————-Shows as 04-JAN-18
TO_CHAR(ADDED_TS, ‘MM/DD/YYYY’) ————————————-Shows as 01/04/2018 in Character format
TO_DATE(to_char(ADDED_TS, ‘MM/DD/YYYY’), ‘MM/DD/YYYY’) — Shows as 1/4/2018 12:00:00 AM
Appreciate any assistance.
Thomas Conrad over 5 years ago
I think you are asking that the results in the query window format the timestamp as mm/dd/yyyy. To do this in Aqua Data Studio, go to File->Options->Results Format->Oracle 9i/10g/11g/12c, select timestamp and enter the format that you want. See attached screenshot. You can see from the before and after attached screenshots that the format was changed to mm/dd/yyyy. Please give that a try.
Thanks Tom. That method worked except I had to use M/dd/yyy . Appreciate the quick response.
Great to hear that it worked for you.