SQL20448N "05/09/2016" cannot be interpreted using format string "YYYY-MM-DD" for the TIMESTAMP_FORMAT function

I am getting below error in db2 when executing the sql query-


SQL20448N "05/09/2016" cannot be interpreted using format string
"YYYY-MM-DD" for the TIMESTAMP_FORMAT function.

here is my sql qyery-

SELECT DLR_CD,
       FIN ,
       YEAR(TO_DATE(CURRENT_DATE ,'YYYY-MM-DD'))-YEAR(TO_DATE(CRTNG_DTE,'YYYY-MM-DD'))
       AS AGE 
FROM ASPECT.RO_CAR_PARK_EXTRACT WHERE YEAR = PARM_YEAR

what could be the reason for this?

Database - DB2 v9.7.200.358
Platform- home basic 7

The error message is very clear. I suppose the type of CRTNG_DTE is text/varchar and at least one of the values is not in the YYYY-MM-DD format (05/09/2016 clearly isn't!) and the conversion TO_DATE(CRTNG_DTE, 'YYYY-MM-DD') fails.

The only unclear in the message is the mention of TIMESTAMP_FORMAT() function. This part of the DB2 docs, function TO_DATE() clears that:


The TO_DATE scalar function is a synonym for the TIMESTAMP_FORMAT scalar function.

The confusion may stem from wrong assumptions about what the TO_DATE() function does.

The TO_DATE(CRTNG_DTE, 'YYYY-MM-DD') is trying to convert strings that have the format YYYY-MM-DD to a date. The dates internal format is irrelevant and you cannot modify it. You cannot choose what format the dates are internally saved as.

Their internal format is a representation of date that is different from DBMS to DBMS and may also change from version to version. And developers need not care what that representation is!

Another issue is the first conversion:

YEAR(TO_DATE(CURRENT_DATE, 'YYYY-MM-DD'))

Since TO_DATE() is expecting a string (to be converted) and a format string but you pass to the function a date and a format string, DB2 converts first the date value (CURRENT_DATE) to a string (I guess using the default in your installation output format of MM/DD/YYYY) so it then tries to calculate:

TO_DATE('05/09/2016', 'YYYY-MM-DD')

which of course fails for the same reason as described above.

So, to solve the issue (or issues):

• Use YEAR(CURRENT_DATE) instead for the first calculation.

• Make sure that all values of the CRTNG_DTE column have been saved with the format ('YYYY-MM-DD') you expect.

StackExchange Database Administrators Q#137972, answer score: 5