Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2009
    Posts
    4

    Answered: Updating the Year Portion of a date field

    On DB2 Z/OS Version 10
    Is it possible to update the year portion of a date field?

    I have tried the following:

    UPDATE TABLENAME
    SET YEAR(BIRTH_DT) = 1900
    WHERE USER_ID_GEN = 79

    And 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


    The following command works, however, USER_ID_GEN has to be updated each time which is inconvenient when there are 7800 rows. If I use "WHERE USER_ID_GEN > 0," I get an error.

    UPDATE TABLENAME
    SET BIRTH_DT = DATE((YEAR) || '-' || (MM) || '-' || (DD))
    WHERE USER_ID_GEN = 23;

    Does anyone have any suggestions?

    Thank you!

  2. Best Answer
    Posted by Stealth_DBA

    "scddsn, one problem you will have to deal with is Leap years. Just concatenating 1900 the the rest of the Month and Day values could result in an invalid date( 2016-02-29 set to 1900-02-29 will give you an error).

    If you want DB2 to convert these Leap year dates to the Last day of February, you can use date arithmetic, instead.
    Code:
    WITH DATE_TAB (DATE_COL)
    AS (
         SELECT DATE('1891-10-31') FROM SYSIBM.SYSDUMMY1 UNION ALL
         SELECT DATE('1900-01-01') FROM SYSIBM.SYSDUMMY1 UNION ALL
         SELECT DATE('1989-12-31') FROM SYSIBM.SYSDUMMY1 UNION ALL
         SELECT DATE('2001-03-01') FROM SYSIBM.SYSDUMMY1 UNION ALL
         SELECT DATE('2016-02-29') FROM SYSIBM.SYSDUMMY1
       )
    SELECT DATE_COL
      , DATE_COL - (YEAR(DATE_COL) - 1900) YEARS AS CONVERT_DATE
    FROM DATE_TAB
    ;
    
    DATE_COL    CONVERT_DATE
    ---------+---------+----
    1891-10-31  1900-10-31
    1900-01-01  1900-01-01
    1989-12-31  1900-12-31
    2001-03-01  1900-03-01
    2016-02-29  1900-02-28
    This subtracts 1900 from the Year part of the Date to get the Offset and uses that Offset to subtract that number of Years from the original value. And as you can see by the last row, it changes the Leap Year 29 Feb to the last day of Feb for the Year it is changed to."


  3. #2
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    110
    Provided Answers: 13
    I didn't get where these YEAR, MM, DD come from.
    If you need to update the year part of a date field only leaving month and day the same, then try this:

    Set birth_dt=date(digits(dec(1900, 4))||'-'||digits(dec(month(birth_dt), 2))||'-'||digits(dec(day(birth_dt), 2)))
    Regards,
    Mark.

  4. #3
    Join Date
    Jul 2009
    Posts
    4
    I will give this a try, thank you.

  5. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    scddsn, one problem you will have to deal with is Leap years. Just concatenating 1900 the the rest of the Month and Day values could result in an invalid date( 2016-02-29 set to 1900-02-29 will give you an error).

    If you want DB2 to convert these Leap year dates to the Last day of February, you can use date arithmetic, instead.
    Code:
    WITH DATE_TAB (DATE_COL)
    AS (
         SELECT DATE('1891-10-31') FROM SYSIBM.SYSDUMMY1 UNION ALL
         SELECT DATE('1900-01-01') FROM SYSIBM.SYSDUMMY1 UNION ALL
         SELECT DATE('1989-12-31') FROM SYSIBM.SYSDUMMY1 UNION ALL
         SELECT DATE('2001-03-01') FROM SYSIBM.SYSDUMMY1 UNION ALL
         SELECT DATE('2016-02-29') FROM SYSIBM.SYSDUMMY1
       )
    SELECT DATE_COL
      , DATE_COL - (YEAR(DATE_COL) - 1900) YEARS AS CONVERT_DATE
    FROM DATE_TAB
    ;
    
    DATE_COL    CONVERT_DATE
    ---------+---------+----
    1891-10-31  1900-10-31
    1900-01-01  1900-01-01
    1989-12-31  1900-12-31
    2001-03-01  1900-03-01
    2016-02-29  1900-02-28
    This subtracts 1900 from the Year part of the Date to get the Offset and uses that Offset to subtract that number of Years from the original value. And as you can see by the last row, it changes the Leap Year 29 Feb to the last day of Feb for the Year it is changed to.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •