RE: Date arithmetic in SQL

Sure, but you'll first have to cast that numeric field into a true DATE
field.

Try this in a STRSQL session:

SELECT CURRENT_DATE + 3 DAYS FROM SYSIBM/SYSDUMMY1

The example above is adding 3 days to the current date and returning that
date (11/21/2008 in this case).

If the field in your file is numeric, I recommend using DIGITS built-in to
cast it to character and then using concatenated substrings to build a
character representation of date, then finally cast it to a DATE data type.
For example, assuming MMDDYYYY format in your field:

select DATE(substr(DIGITS(numericField),1,2) || '/' ||
substr(DIGITS (numericField),3,2) || '/' ||
substr(DIGITS (numericField),5,4)) + 33 DAYS as NewDate

from myLib/myFile

Same goes for subtraction.

Elvis

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


-----Original Message-----
Subject: Date arithmitic in SQL

Is there a way to add or subtract a duration (# of days) to a Date field in
SQL.
What I have is a field containing a numeric representation of a date in a
file.
I want to use SQL to increment that field by a specific value.

I am using a V5R3 system.

Thanks,
 
Jeff Young