I've been searching for an hour now and can't seem to find an answer. My prior sql experience is with ms sql server, oracle, etc.. so I'm not finding the functions I need, probably because i'm searching using the wrong approach for the problem.. that said, our company was recently bought by a corporation that uses AS400's exclusively, so I have a bit of a learning curve ahead of me as a web intranet app developer I guess:

At any rate, here's my problem:

On the AS400, patient admission dates are stored as a number in this format, YYYYMMDD. What I need to do, is select only the prior 10 days of patient admissions, including the current day.

I've discovered the "Current Day - 10 Day" type approach, but since the admission date is a number, not a date, it won't work. So, I've been trying to find a way to convert the YYYYMMDD number to a date. On IBM's site, they reference a "TO_DATE" function, as well as a "DATE()" function. "TO_DATE" returns 'not found in library', and "DATE()" returns 'conversion error' when I try to feed it the YYYYMMDD number.

The only way I could find to select the prior 10 days, was like so:

Where Substr(Char(pt_admission_date), 1, 4) = Year(Current Date - 10 days)
and Substr(Char(pt_admission_date), 5,2) = Month(Current Date -10 days)
and Substr(Char(pt_admission_date), 7,2) = Day(Current Date - 10 Days)

I know I must be missing something here. There must be a easier way to just convert YYYYMMDD to a date, and subtract 10 days from it.

edit:
Current Date - 10, would then need to be followed by another set, like OR (Current Date - 9), OR Current Date - 8, etc.. which I'm sure has an easier solution also. Thanks.

DB2/400 is a little different than the other DB2's, and I don't have a DB2/400 manual, but try this:

WHERE ADMISSION_DATE >= (CURRENT DATE – 10 DAYS)

If there is an index on admission_date then it might be more efficient to compute current date - 10 days in your program and then issue the command (but not sure if this is necessary). You can get this by:

SELECT (CURRENT DATE - 10 DAYS) INTO :DATE_HOST_VARIABLE FROM ANY-TABLE

Sorry, I just noticed that the admission_date is not stored as a DB2 date column, but just a number?

If you use:
SELECT (CURRENT DATE - 10 DAYS) INTO :DATE_HOST_VARIABLE FROM ANY-TABLE

Maybe you can parse the host variable and build the numeric number you need to compare dates.

But I am not exactly sure what the date format is in your application because I know that at one time (not sure if this is still true) the same table/file can be accessed by DB2 or the native AS/400 file system.

where pt_admission_date = date(days(current date) - 10)



date(days(current date) - 10) this will

Here is Example :-

current Date

select current date from sysibm.sysdummy1


----------
2003-10-29

current date + 10 days, you can what evere days add or substract


select date(days(current date) + 10) from sysibm.sysdummy1


----------
2003-11-08

Let me know if any questions


LekhaRaju Ennam

you do now

DB2 Universal Database for iSeries SQL Reference


rudy
http://r937.com/

I think I will pass.

pass?

i wasn't suggesting a download, that's an online manual, bookmark it and you'll never be without it again (as long as you're online)

I don't work with DB2/400. I just try to help people who have questions. I already handle (and am certified by IBM as a DBA in) OS/390, z/OS, Linux, UNIX, and Windows. That's enough for me.

Hi,

could you plz tell how to convert number (20071123) to char '20071123' then back to the date format '2007/11/23' (yyyy/mm/dd)

Thanks & Regards,
Venkatesh Gubba
gubba.venkatesh@gmail.com

wow, gubba, this thread is 7½ years old!!

you win the prize for oldest dormant thread resurrected