RE: SQL to increment date

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

It's possible but very ugly.  Convert decimal to char, char to date, add one
day, convert date to char, char to decimal.

Something like:

update ABC36f/FILE set PDATE = 
  decimal(substr(char(date(                                  
  substr(digits(decimal(PDATE,8,0)),1,4) || '-' ||              
  substr(digits(decimal(PDATE,8,0)),5,2) || '-' ||              
  substr(digits(decimal(PDATE,8,0)),7,2)) + 1 day,USA),7,4) ||  
  substr(char(date(                                          
  substr(digits(decimal(PDATE,8,0)),1,4) || '-' ||              
  substr(digits(decimal(PDATE,8,0)),5,2) || '-' ||              
  substr(digits(decimal(PDATE,8,0)),7,2)) + 1 day,USA),1,2) ||  
  substr(char(date(                                          
  substr(digits(decimal(PDATE,8,0)),1,4) || '-' ||              
  substr(digits(decimal(PDATE,8,0)),5,2) || '-' ||              
  substr(digits(decimal(PDATE,8,0)),7,2)) + 1 day,USA),4,2),9,0)
where the Pdate = 20071219

This is a perfect place for a UDF.  It would hide the complexity and the UDF
code would actually be simpler as it could use local variable to effect a
lot of this conversion work.

Elvis

Celebrating 10-Years of SQL Performance Excellence on IBM i5/OS and OS/400
www.centerfieldtechnology.com
 

-----Original Message-----
Subject: SQL to increment date

 Iam looking for an SQL to increment   date by one day.
  update ABC36f/FILE set PDATE = pdate+1 where the Pdate= 2007/12/19.
   
  PDATE is defined as 9decimal. Can some one advise me how to do work this
out?

Thanks,

Donna.