Search code examples
datedb2zosdb2-zos

Update the YEAR on a DATE field

DB2 z/OS Version 10

For a DATE column in a table I would like to update the YEAR portion of the DATE to 1900. For example, update 1981-09-03 to 1900-09-03.

I have attempted using the following:

 UPDATE SERDB.S16_WEB_USERS_T
       SET YEAR(S16_BIRTH_DT) = '1900'
     WHERE YEAR(S16_BIRTH_DT) > '0';

I receive the following error:

ILLEGAL SYMBOL "(". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: = .. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.67.27 SQL Code: -104, SQL State: 42601

Does anyone have any suggestions? Thank you.

Solution

  • AFAIK, you can't use YEAR() to set a value in a date field in any platform or version of DB2.

    Going to have to do some math

     UPDATE SERDB.S16_WEB_USERS_T
       SET S16_BIRTH_DT = S16_BIRTH_DT - (YEAR(S16_BIRTH_DT) - 1900) YEARS 
     WHERE YEAR(S16_BIRTH_DT) > '0';

    But I question why you want to do this. Especially with the WHERE YEAR(S16_BIRTH_DT) > '0'.

    Also note that if the birthdate is on the 29th of February, the date will be changed to the 28th of February 1900.