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_DATEscalar function is a synonym for theTIMESTAMP_FORMATscalar 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):
YEAR(CURRENT_DATE) instead for the first calculation.CRTNG_DTE column have been saved with the format ('YYYY-MM-DD') you expect.
