CODEWITHSUNDEEP
sqloracle-databasedatetime
Larry C. Lyons
Larry C. Lyons

Reputation: 403

Oracle sql to_char date conversion produces invalid output

We're using Oracle 11g here with some pretty vanilla SQL. Due to some new client requirements we need to output submission dates and times with milliseconds to 3 decimal places. Since they also want legacy data also to have the same output I've came up with the following:

SELECT
NVL( CD.RECEIPT_DATE_TS, CAST(CD.RECEIPT_DATE AS TIMESTAMP) ) as receipt_ts,
TO_CHAR(NVL( CD.RECEIPT_DATE_TS, CAST(CD.RECEIPT_DATE AS TIMESTAMP) ) , 'YYYY-MM-DD HH24:MM:SSxFF3' ) as RECEIPT_TS_conv,
etc.

However the output shows the following:

receipt_ts_no_convert          receipt_ts_conv
4/16/2018 5:08:10.657463 PM    2018-04-16 17:04:10.657
4/16/2018 12:00:00.000000 AM   2018-04-16 00:04:00.000
4/13/2018 7:48:41.105792 PM    2018-04-13 19:04:41.105

As you can see on those entries with a time like 4/16/2018 12:00:00.000000 AM appears to add the month number to the minutes, as in 2018-04-16 00:04:00.000.

Ideally the output for that entry should look be 2018-04-16 00:00:00.000.

I'm not sure of what's going on, and am not sure how to correct this. Any suggestions on how to fix this would be most appreciated.

Many thanks.

larry

Upvotes: 0

Views: 50

Answers (1)

Mark J. Bobak
Mark J. Bobak

Reputation: 14393

In your date format, you are using MM for both month and minute. MM is month. Use MI for minutes.

Upvotes: 4

Related Questions