SQL query: Fetch all records from date field

2640 pts.
Tags:
AS/400
SQL
SQL Query
Hi,
How to fetch all records from the date field less than current date's records provided date field format is centuray(C):YY/MM/DD
Thanks.
0

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 7  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • philpl1jb

    So you say date (C):YY/MM/DD

    Do that mean your field is 7 numeric? I don't think a date type field can be formatted as you've shown.

    54,090 pointsBadges:
    report
  • 6r
    yes it's true it's 7 digit numeric field and up on running sql query it shows date field in same formart as told previously.
    Main objective is to fethc all the records based on lesser date than today's date so that they could be categorized accordingly.
    2,640 pointsBadges:
    report
  • carlosdl
    What problem are you having?

    A WHERE condition on that field is all you need.  Did you try?

    I suppose the are some complications, but if you don't tell us, we don't have a way to know.
    84,805 pointsBadges:
    report
  • ToddN2000
    Post the code you have tried and any errors you are getting.
    133,790 pointsBadges:
    report
  • philpl1jb

    This would produce today in form 1yymmdd

    Select '1' ||substring(char(current date),3,2) ||substring(char(current date),6,2) || substring(char(current date),9,2) from sysibm.sysdummy1


    For your purposes, you will what to use that logic in the where clause

    Select * from myTable

    where MyDateFld < DEC('1' ||substring(char(current date),3,2) ||substring(char(current date),6,2) || substring(char(current date),9,2),7,2)

    54,090 pointsBadges:
    report
  • 6r

    There is error for below query:-

    "Selection error involving field *N."

    Select * from myTable

    where MyDateFld < DEC('1' ||substring(char(current date),3,2) ||substring(char(current date),6,2) || substring(char(current date),9,2),7,2)

    also is current date system defined current date field?

    how will above query justify that in current date there should only be value of today's date?

     

    Thanks.

     

    2,640 pointsBadges:
    report
  • ToddN2000
    Check your current date value. Before you do anything with it. When I try it, it returns MM/DD/YY. What format is your date using? Are you running this SQL from the i-series or another SQL application?
    133,790 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: