Convert date from yyyy-mm-dd to yyyy-mm and replace it to yyyymm in SQL (db2)-CodePudding

The following code was used when the user passed a parameter for example: 2022-03-25 and converted it to 20220325

CASE WHEN LENGTH('$P!{DATE}') = 10
     THEN REPLACE(SUBSTR('$P!{DATE}', 1, 10), '-', '')
     ELSE '$P!{DATE}' END

However now I need instead of converting to 20220325 to just 202203, any help?

CodePudding user response：

There doesn't seem to have been any purpose for the substr() after it was determined the length is 10. But as that appears to work, just eliminate the last three characters. (Last two would also ultimately still work.)

CASE WHEN LENGTH('$P!{DATE}') = 10
     THEN REPLACE(SUBSTR('$P!{DATE}', 1, 7), '-', '')
     ELSE '$P!{DATE}' END