SQL
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 begginner stuff, that would be great. Remeber I'm a RPG programer not an SQL junkie yet. I like it so far. Very powerfull. Thanks in advance...
Software/Hardware used:
Software/Hardware used:
ASKED:
January 23, 2007 4:53 PM
UPDATED:
June 20, 2008 6:30 PM
Answer Wiki:
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 (sol@linker.com)
Linker Systems, Inc. (www.linkersystems.com)
800-315-1174 (+1-949-552-1904)
To see all answers submitted to the Answer Wiki: View Answer History.
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.
You may also wish to visit SQL and SQL Server Tutorial and Reference Guide by fellow member Mr. Denny.