DB2 z/OS - Get the last 2 digits of year

0
votes
SELECT
     SUBSTR(CAST(YEAR(SOMEDATE) AS VARCHAR(4)),2,2) AS "YY" 
FROM SOMESCHEMA.FOO;

Gets me the following error:

  1. [Code: -104, SQL State: 42601] ILLEGAL SYMBOL ",2". SOME SYMBOLS THAT MIGHT BE LEGAL ARE:. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.26.14
  2. [Code: -514, SQL State: 26501] THE CURSOR SQL_CURLH200C1 IS NOT IN A PREPARED STATE. SQLCODE=-514, SQLSTATE=26501, DRIVER=4.26.14

And no matter what I change, the error stays..

1
sqldb2substrdb2-zos
how are you submitting the SQL? From which tool? Some details missing from question. If you try with an interactive SQL tool , and adjust the query to replace SOMEDATE by CURRENT DATE (just for testing), and replace the table name by sysibm.sysdummy1 (just for testing), you may get more details. Sometimes, what gets executed is not what you assume. – mao
I use DBVisualizer. Seems unlikely that this simple SQL statement is not executed correctly. – Spektakulatius
Does the statement run when submitted via a different non-java tool , for example via odbc or CLI, or directly via ISPF or DB2I, SPUFI etc. ? – mao

1 Answers

0
votes

The solution for DB2 z/OS is:

VARCHAR_FORMAT(SOMEDATE, 'YY') AS "YY"