query 400 or Sequel statement for month

pts.
Tags:
AS/400
IBM
I have a field in AS400 called CSDATE that gives year and month in the following format: 200511 I want to create query statement to compare current year and month to select only those records from the same year, prior month. For example, I would run the report on December 1st 2005 and want to retrieve all records from November 2005. any help would be greatly appreciated.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Use the following:

SELECT fields
FROM table
WHERE csDate=SUBSTR(TO_CHAR(CURRENT DATE-1 MONTH,USA),7)||
SUBSTR(TO_CHAR(CURRENT DATE-1 MONTH,USA),1,2)


Sheldon Linker
Linker Systems, Inc.
www.linkersystems.com
sol@linker.com
800-315-1174
+1-949-552-1904

Discuss This Question: 5  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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Snowiboy
    I get "missing required parameter" when I check that SQL statement?
    0 pointsBadges:
    report
  • SheldonLinker
    Hmm... Looks like the version you're using requires 3 arguments to all SUBSTRs. Use 7,4 instead of 7 in the first SUBSTR.
    30 pointsBadges:
    report
  • Snowiboy
    Now it is barking about data types...I apologize for my lack of knowledge on this topic, but really do appreciate your assistance. The CSDATE field is showing Znd 6,0 So I'm assuming it is a numberic field? How do I address that?
    0 pointsBadges:
    report
  • SheldonLinker
    Ok, here's the same thing, but set for numbers: SELECCT fields FROM table WHERE csDate=YEAR(CURRENT DATE-1 MONTH)*100 + MONTH(CURRENT DATE-1 MONTH)
    30 pointsBadges:
    report
  • Snowiboy
    SheldonLinker I owe you big time! It worked. This forum has been of such benefit to me and our company it's hard to recall how things were solved before. Thanks again!
    0 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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

Following