Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2003
    Posts
    1

    Unanswered: sql question: convert number to date or..

    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.
    Last edited by jwhitener; 10-23-03 at 19:40.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.

  4. #4
    Join Date
    Oct 2003
    Posts
    8

    Re: sql question: convert number to date or..

    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

    Originally posted by 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.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Originally posted by 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


    rudy
    http://r937.com/

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I think I will pass.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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)

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.

  9. #9
    Join Date
    Apr 2011
    Posts
    2

    how to convert number to date format..

    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

    Quote Originally Posted by db2os390udbdba View Post
    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

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    wow, gubba, this thread is 7 years old!!

    you win the prize for oldest dormant thread resurrected

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •