Oracle Timestamp type convert to Date

by Nov 2, 2017

Good Afternoon.

I am using ADS version 18.0.18-5 with an Oracle database version 11.2.0.3.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.

Thanks

Gregg

Response

Thomas Conrad over 5 years ago
Hi Gregg,

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

Attachments.zip

Gregg Blachstein over 5 years ago
Thanks Tom. That method worked except I had to use M/dd/yyy . Appreciate the quick response.

Gregg

Thomas Conrad over 5 years ago
Hi Gregg,

Great to hear that it worked for you.

Thanks, Tom