SQL Date Arithmetic

[gregwga50]

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.

[gregwga50]

I plan to put this in an SQL program. Sorry, I don't have the capability to update my posts or I would changed the previous one

[MFisher]

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 ?

https://www.rpgpgm.com/2013/09/more-sql-and-dates-part-i.html

[Barbara Morris]

Watch out subtracting 10000 from 20200229 (20190229 is an invalid date).

[gregwga50]

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 ?

https://www.rpgpgm.com/2013/09/more-sql-and-dates-part-i.html

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

[inigo.redin]

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.

[Vectorspace]

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.

You are transforming the table column (DATRES) into a different format, to match the format of the comparison value ("current_date - 1 year"). In this case converting the numeric column to date type, to match against a date comparison value. This is inefficient because then the SQL engine cannot use any indexes that might exist on DATRES (unless someone explicitly created an index on "to_date(DATRES,'YYYYMMDD')" ).

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.

select * from MYTABLE
 where DATRES < dec(current_date - 1 year,8,0)

Converting a date to numeric using dec(<date>,8,0) puts it into yyyymmdd format

[inigo.redin]

Yes, you're right.
That's what happens when you type fast and you don't think ;-)

Better transform just once instead every record.

[B.Hauser]

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:

Dec(Date(Digits(YourDate) concat '000000') - 1 Year, 8, 0)

Birgitta

[inigo.redin]

Birgitta, when I run that sentence, I get a null value:

values Dec(Date(Digits(20200207) concat '000000') - 1 Year, 8, 0);

Can I convert from numeric to date simply using the function DATE?

[B.Hauser]

values Dec(Date(Digits(20200207) concat '000000') - 1 Year, 8, 0);

If you specify a number without decimal positions it is interpreted as integer ... and integer has 10 digits, so in your example the expression is 0020200207000000 which is NOT a valid character representation of a date or timestamp.
Your example should work if you change it as follows:

Values(Dec(Date(Right(Digits(20200207) concat '000000', 14)) - 1 Year, 8, 0));

You can convert a numeric date with the DEC scalar function if the numeric value represents the (calculated) number of days since 0001-01-01.

Values(Date(737462))

will return February, 7 2020.

Birgitta

[gregwga50]

These are great tips, this is exactly what I need. Thanks to everyone.