· 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.


Discussion (8)0
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>]