Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2008
    Posts
    94

    Unanswered: Updating the values with date YYYYMMDD

    I have the below values in a column called COLUMN1

    DS_AC_SS_20080122
    DS_CS_SS_20080122
    DS_CR_SS_20080122
    DS_AS_SS_20080122
    DS_TN_SS_20080122
    DS_BS_SS_20080122
    DS_CU_SS_20080122

    how to update the above values into below by using single update statement

    DS_AC_SS_20080123
    DS_CS_SS_20080123
    DS_CR_SS_20080123
    DS_AS_SS_20080123
    DS_TN_SS_20080123
    DS_BS_SS_20080123
    DS_CU_SS_20080123

    Note: 20080123 is YYYYMMDD when ever i update the column should update with +1 day

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    There will be many ways.

    Here is an example.
    Code:
    UPDATE .....
       SET column1
         = SUBSTR(column1, 1, 9)
           || TRANSLATE( 'abcdefgh'
                       , CHAR( DATE( TRANSLATE( 'abcd-ef-gh'
                                              , SUBSTR(column1, 10, 8) 
                                              , 'abcdefgh')
                                   ) + 1 DAY
                             , ISO)
                       , 'abcd-ef-gh')
    .....
    ;

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If you are using DB2 V9.5, you can use TO_CHAR and TO_DATE.
    Code:
       SET column1
         = SUBSTR(column1, 1, 9)
           || TO_CHAR(TO_DATE(SUBSTR(column1, 10, 8), 'YYYYMMDD') + 1 DAY, 'YYYYMMDD')

Posting Permissions

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