Updating the values with date YYYYMMDD

laknar · #1 (**permalink**) 03-30-09, 00:15

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

tonkuma · #2 (**permalink**) 03-30-09, 01:01

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')
.....
;

tonkuma · #3 (**permalink**) 03-30-09, 01:39

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')

Internet Services

Web Development

Digital Marketing

Consumer Tech