Tags: convert, database, date, db2, experience, mysql, number, oracle, prior, searching, server, sql

sql question: convert number to date or..

On Database » DB2

5,848 words with 7 Comments; publish: Tue, 04 Dec 2007 18:05:00 GMT; (25046.88, « »)

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.

All Comments

Leave a comment...

  • 7 Comments
    • 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

      #1; Tue, 11 Dec 2007 17:49:00 GMT
    • 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.

      #2; Tue, 11 Dec 2007 17:50:00 GMT
    • 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

      === Original Words ===

      jwhitener

      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.

      #3; Tue, 11 Dec 2007 17:51:00 GMT
    • === Original Words ===

      Marcus_A

      DB2/400 is a little different than the other DB2's, and I don't have a DB2/400 manual...

      you do now ;)

      DB2 Universal Database for iSeries SQL Reference (http://publib.boulder.ibm.com/iseries/v5r2/ic2924/info/db2/rbafzmst02.htm)

      rudy

      http://r937.com/

      #4; Tue, 11 Dec 2007 17:52:00 GMT
    • I think I will pass.
      #5; Tue, 11 Dec 2007 17:53:00 GMT
    • 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)

      #6; Tue, 11 Dec 2007 17:54:00 GMT
    • 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.
      #7; Tue, 11 Dec 2007 17:55:00 GMT