Meenakshi Shanmugam · Jul 15, 2020

TO_CHAR Function not working for 12/31/1840 in Cache DB


Below To_Char function is not working as expected

SELECT TO_CHAR('12/31/1840','mm/dd/yyyy') ---01/12/1841

I expect the result to be 12/31/1840. Let me know If there are any suggestion to get the correct result.


0 256
Discussion (8)2
Log in or sign up to continue

Maybe you want to use TO_DATE(...)  but if you want to use TO_CHAR() then you have to supply a correct argument, which is in this case a $HOROLOG value

SELECT TO_CHAR(0,'mm/dd/yyyy') ---01/12/1840

SELECT TO_CHAR(65575,'mm/dd/yyyy') ---15/07/2020

TO_CHAR(): A string function that converts a date, timestamp, or number to a formatted character string.

TO_DATE(): A date function that converts a formatted string to a date.

When I use TO_DATE function I am getting error. Please see below

>[Error] Script lines: 1-1 --------------------------
 [SQLCODE: <-400>:<Fatal error occurred>]
[Cache Error: <<ILLEGAL VALUE>todate+32^%qarfunc>]
[Location: <ServerLoop - Query Open()>]
[%msg: <Unexpected error occurred:  <ILLEGAL VALUE>todate+32^%qarfunc>]


but this is working .  Result : 8/5/2020


Of course works, because the format part MATCHES the date part!

Did you read the documentation?

TO_DATE(datestring [,format])

Your datestring is 5/8/2020 but your format is 'YYYY-MM-DD' which does not match '5/8/2020'!!

I would like to change the date 5/8/2020 to YYYY-MM-DD format i.e 2020-08-05 What function I need to use for that?

What about?

select TO_CHAR(TO_DATE('5/8/2020','MM/DD/YYYY'),'YYYY-MM-DD')


will also work.