I have a file, I'll call MYFILE that has a field that represents a date. The field is named DATRES and is (8,0) S in YYYYMMDD format. I need to run this SQL command weekly that will delete records where DATRES converted to a date is more than a year old.
Announcement
Collapse
No announcement yet.
SQL Date Arithmetic
Collapse
X
-
If it's simply 1 year, you can subtract 10000 from YYYYMMDD to get 1 year ago. so 20200206 becomes 20190206. For more complicated date calculation, maybe you can find help here ?
More about using SQL with date fields: using the functions DAY, DAYOFWEEK, WEEK, MONTH, YEAR, and the supported formats of dates
Comment
-
Originally posted by MFisher View PostIf it's simply 1 year, you can subtract 10000 from YYYYMMDD to get 1 year ago. so 20200206 becomes 20190206. For more complicated date calculation, maybe you can find help here ?
It could very well be something else beside 1 year, if the powers that be decide on differing criteria. I need to be able to do this whether it's 6 months, 30 days etc. I just picked 1 year arbitrarily
Comment
-
Hi.
An SQL approach could be:
DELETE FROM MYFILE
WHERE TO_DATE(CHAR(DATRES ), 'YYYYMMDD') < CURRENT_DATE - 1 YEAR;
(Try with SELECT first)
You can substitute "1 year" with "1 week", "1 day", "26 months"... whatever!
Be sure in DATRES you always have a correct date, if not, you'll nedd to add extra sentences to avoid problems.
Comment
-
Originally posted by inigo.redin View PostHi.
An SQL approach could be:
DELETE FROM MYFILE
WHERE TO_DATE(CHAR(DATRES ), 'YYYYMMDD') < CURRENT_DATE - 1 YEAR;
(Try with SELECT first)
You can substitute "1 year" with "1 week", "1 day", "26 months"... whatever!
Be sure in DATRES you always have a correct date, if not, you'll nedd to add extra sentences to avoid problems.
It's better to transform your comparison value to match the column format. I.e. transform the date "current_date - 1 year" to numeric yyyymmdd. This also avoids the issue of invalid dates in the table column.
Code:select * from MYTABLE where DATRES < dec(current_date - 1 year,8,0)
Comment
-
If you only need the numeric date without and a valid date is not important, so you simply can subtract 10000.
Converting a real date into a numeric date can now easily be done with the DEC scalar function.
But if you need to subtract 1 year from a numeric date and the resutl must be again a valid (numeric) date, the easiest way with SQL is:
Code:Dec(Date(Digits(YourDate) concat '000000') - 1 Year, 8, 0)
Last edited by B.Hauser; February 7, 2020, 03:41 AM.
Comment
-
Code:values Dec(Date(Digits(20200207) concat '000000') - 1 Year, 8, 0);
Your example should work if you change it as follows:
Code:Values(Dec(Date(Right(Digits(20200207) concat '000000', 14)) - 1 Year, 8, 0));
Code:Values(Date(737462))
BirgittaLast edited by B.Hauser; February 7, 2020, 06:19 AM.
Comment
Comment