as400 date query

To query the last 7 days from today in as400 as it stores the dates in char type how to retrieve the results from today as i tried using such as

        where chardate >= char(days(curdate()) + 7)

but its still not working开发者_运维知识库

There are two parts to the answer. The first involves date math on this particular flavor of DB2. The DB2 expression equivalent to curdate()) + 7 is current date + 7 days.

The second involves converting date values to character values (or vice versa) so we can compare them. We need to know what format chardate stores dates in before we can really crack that one. Let's assume it's in YYYY-MM-DD format. Then you can use char(current date + 7 days, iso) to get seven days in the future in the same format.

So, with that assumption, your where statement would be

where chardate >= char(current date + 7 days, iso)

There are several standard date formats that date can convert to:

• ISO: YYYY-MM-DD
• USA: MM/DD/YYYY
• EUR: DD.MM.YYYY
• JIS: YYYY-MM-DD

If your chardate is in a different format, you will need to do some rather fiddly work with substr. For example, to convert YYYY-MM-DD to YYYYMMDD you'd need something like

substr(char(current date, iso), 1, 4) ||
substr(char(current date, iso), 5, 2) ||
substr(char(current date, iso), 7, 2)

A major problem with this method is that formats that aren't stored in "year month day" order can't be reliably compared. That is, 12311969 (i.e., MMDDYYYY) will compare as greater than 01012011, since as far as the database is concerned, you're comparing two eight-digit numbers. (That's why you should almost always store dates in actual date fields or in YYYYMMDD or similar properly ordered format.)

I've had great success using a free utility called idate, which provides SQL user-defined functions (UDFs) to convert dates stored in char & numeric fields into dates. Note that this solution requires the availability of an RPG compiler.

For the last 7 days from today:

where 
date(substr(chardate,1,4) || '-' || 
substr(chardate,5,2) || '-' || 
substr(chardate,7,2)) between current date - 7 days and current date

To perform a character range comparison:

where
chardate between 
substr(char(current date - 7 days, iso),1,4) ||
substr(char(curernt date - 7 days, iso),6,2) ||
substr(char(current date - 7 days, iso),9,2)
and 
substr(char(current date, iso),1,4) ||
substr(char(current date, iso),6,2) ||
substr(char(current date, iso),9,2)