Primary tabs

Question
Meenakshi Shanmugam · Jul 15, 2020

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

Hi,

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.

Thanks,
Meena

00
0 8 104 2

Replies

Julius Kavay · Jul 15, 2020

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.

00 0
 
Meenakshi Shanmugam · Jul 15, 2020

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>] 

SELECT TO_DATE('5/8/2020','YYYY-MM-DD')
00 0
 
Meenakshi Shanmugam · Jul 15, 2020

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

SELECT TO_DATE('5/8/2020','DD/MM/YYYY')
00 0
 
Julius Kavay · Jul 15, 2020

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

00 0
 
Julius Kavay · Jul 15, 2020

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'!!

00 0
 
Meenakshi Shanmugam · Jul 15, 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?

00 0
 
Julius Kavay · Jul 15, 2020

What about?

select TO_CHAR(TO_DATE('5/8/2020','MM/DD/YYYY'),'YYYY-MM-DD')
00 0
Accepted Answer
 
Kevin Chan · Jul 15, 2020

SELECT %ODBCOUT(TO_DATE('5/8/2020','MM/DD/YYYY'))

will also work. 

00 0