Hi everyone! I'm attempting to use the TO_CHAR function on timestamp values in order to convert from the standard timestamp format to a specified date/time format, specifically ‘YYYY-MM-DD hh24:mm:ss’, but this isn't quite working the way I would expect it to work.
Background info:
- I am working on a Windows PC
- I am using Oracle SQL Developer Version 23.1.0.097, Build 097.1607
- And this IDE is connecting to and using an Oracle database, Oracle Enterprise Edition, 19.19
My query is fairly simple, for the sake of this specific issue:
SELECT DISTINCT
TO_CHAR(EFFECTIVE_DATE, 'YYYY-MM-DD hh24:mm:ss'), EFFECTIVE_DATE
FROM [table]
ORDER BY EFFECTIVE_DATE DESC;
My results, when using this query are:
You can see here that the values in the two columns don't actually match, or at least not the way I would expect them to. Perhaps I am just not understanding how to read a timestamp or how the TO_CHAR interprets the timestamp value? I would welcome any insight and explanation if that is the case.
I would have expected TO_CHAR(EFFECTIVE_DATE, 'YYYY-MM-DD hh24:mm:ss') to convert the following value:
“03-DEC-24 11.52.47.954650000” → “2024-12-03 11:52:47”
(or maybe “2024-12-03 11:52:48” depending on how it rounds, or truncates).
I'm not committed to using TO_CHAR and I'm willing to consider/use other tools/functions to get the desired format. I am only using TO_CHAR here because it is the only formatting option that doesn't give me an error.
I've attempted to use the CONVERT, FORMAT and DATE_FORMAT functions, but I end up with errors using these options.
I also saw a post here that seemed very close to providing a solution, but when I attempt to convert my timestamp value into a timestamp, using TO_TIMESTAMP, I get the error “format code appears twice” which I am assuming means that I am trying to convert a timestamp into a timestamp. So, I guess my values really are saved as “timestamp” data types.
Any insights or suggestions on how to solve this problem would be greatly appreciated! Thank you all!