CODEWITHSUNDEEP
ibm-midrange
AndyD273
AndyD273

Reputation: 7279

AS400 YYYYMMDD - 1 month

We get data from one of our partners that is running an i5 AS/400 database.
Their date columns are all in YYYYMMDD format. In my query, I want to get only stuff added in the last month.

I know I can get the current_date and subtract 1 month, but it comes in date format.
So my choices are to either take the YYYYMMDD and convert it to date format, or take the current date and change it to YYYYMMDD format.

Since the AS/400 is missing out on the convert command that tsql has, I'm not sure which the best way to go is.

Upvotes: 2

Views: 8820

Answers (3)

Mike Wills
Mike Wills

Reputation: 21255

I learned this trick from the MIDRANGE-L list.

YYYYMMDD <= DEC(REPLACE(CHAR(CURRENT_DATE - 1 MONTH, ISO), '-', ''), 8, 0)

Upvotes: 4

dmc
dmc

Reputation: 2684

I assume you're saying that the date column is actually eight-digit numeric field that happens to contain values that we interpret as dates. I also assume you're running these queries directly against the i5 database.

You will want to convert a date into the YYYYMMDD numeric value, then query against the YYYYMMDD field. Converting the YYYYMMDD value in the database to a date and comparing against that will require each value to be converted, which will eliminate the possibility of using an index over that column. It also opens you up to problems due to invalid dates in the YYYYMMDD field.

Here's an example of converting a date calculation to an eight-digit number as YYYYMMDD:

select year(current_date - 1 month) * 10000 +
       month(current_date - 1 month) * 100 + 
       day(current_date - 1 month)           
from sysibm.sysdummy1

You could use this in a query like this:

select *                              
from libname.tablename                    
where date_field >=                          
  year(current_date - 1 day) * 10000 +
  month(current_date - 1 day) * 100 + 
  day(current_date - 1 day)

An alternative is to use a tool like IDATE. This makes conversions of numerics to dates much easier. Go to this link and search for IDATE for more information about this option.

Upvotes: 1

Jonathan M
Jonathan M

Reputation: 17441

Your best bet is to put the date you're seeking in YYYYMMDD format. That way you're letting the database do its job of comparing data against query specs.

Upvotes: 1

Related Questions