Adding days to a date

[gregwga50]

I have a file that contains a numeric field that I want to add to a date field to come up with another date, Lets say, the field in said file contains 20. What SQL is needed to add 20 to '2017-01-01 to produce '2017-01-21'

This is interactive SQL, btw, not imbedded

[Viking]

An example of adding 20 days to a date:

select current_date + 20 days
from sysibm/sysdummy1

You can substitute a date field from your file instead of current_date and your filename instead of sysibm/sysdummy1.

[CRinger400]

Just FYI anyone, if it's a numeric date stored in format YYYYMMDD then you have to convert it to a native date type first then add days.

/* convert 8p0 to timestamp then date */
select date(concat(MyDateYYMD , '000000')) + 20 days
from MyTable

Ringer

[Scott Klement]

I didn't think the date was numeric?? He says the number of days to add is numeric.... yet none of the replies showed how to use a column as the number of days to add...

[tomliotta]

Well, assuming a collection SQLEXAMPLE with table DATEDATES that has an integer/numeric column DID and a DATE column DT1:

SELECT DID, DT1, (DT1 + DID days) FROM SQLEXAMPLE.DATEDATES

The resulting rows will show the numeric number of days to add, the starting date and finally the resulting date. At least that works on my 6.1 test system.

Example output in STRSQL:

[FONT=courier new]....+....1....+....2....+....3....+....4....+.
          DID   DT1         ( DT1 + DID DAYS )
            1   1900-01-01      1900-01-02    
            2   2014-04-30      2014-05-02    
            3   2014-04-30      2014-05-03    
            3   2014-04-30      2014-05-03    
            3   2014-04-30      2014-05-03    
            3   2014-04-30      2014-05-03    
            3   2014-04-30      2014-05-03    
            3   2014-04-30      2014-05-03    
            3   2014-04-30      2014-05-03    
            3   2014-04-30      2014-05-03    
            3   2014-04-30      2014-05-03    
            3   2014-04-30      2014-05-03    
            3   2014-04-30      2014-05-03    
            3   2014-04-30      2014-05-03    
            3   2014-04-30      2014-05-03    
            3   2014-04-30      2014-05-03    
            3   2014-04-30      2014-05-03    
            3   2014-04-30      2014-05-03    
            3   2014-04-30      2014-05-03     [/FONT]