SQL system date

1,780 pts.
Tags:
Application development
AS/400
CLP
RPG
RPGLE
SQL Server
I have a beginner SQL question. I'm new to using SQL on the iSeries. I want to get the system date and compare it to a date in a file and depending if its 30 days past delete that record. I can get most of it but the system date is driving me nuts. How do I get it and how can I convert the dates to compare then too. Also if anyone has a good reference on the web of good SQL tips and beginner stuff, that would be great. Remember I'm a RPG programer not an SQL junkie yet. I like it so far. Very powerful. Thanks in advance...
ASKED: January 23, 2007  4:53 PM
UPDATED: June 27, 2013  6:18 PM

Answer Wiki

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

Assuming you have a file named myFile, and a date column named dateCol, and a single-character variable named answer, the following will tell you wether the date column is within 30 days of today. I will also assume that there is a key column named keyCol and a key value variable named keyVal.

SELECT CASE WHEN dateCol + 30 DAYS >= CURRENT DATE THEN ‘Y’
ELSE ‘N’ END
INTO :answer
FROM myFile
WHERE keyCol=:keyVal;

Sheldon Linker

Discuss This Question: 2  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
  • PurpleLady
    In tsql - GetDate() will return the current date/timestamp, that is, 2007-01-24 07:02:33.130. You can then convert it to the format matching the database date field using convert. For example, select convert(varchar(10), getdate(), 110) will return 01-24-2007. In addition, there are functions that can be used (dateadd) to adjust the date. I would probably use something like if dbdate > convert(varchar(10), dateadd(dd,-30,getdate()), 110) then delete file. I don't know if iseries SQL is the same, but there should be similar functionality.
    0 pointsBadges:
    report
  • BrentSheets
    You may also wish to visit SQL and SQL Server Tutorial and Reference Guide by fellow member Mr. Denny.
    6,875 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