|
View previous topic :: View next topic
|
| Author |
Message |
alluri12
New User
Joined: 15 Aug 2006
Posts: 44
Location: Virginia, USA
|
|
|
|
Hi,
I have a table
| Code: |
Table1
col1 char(2)
col2 decima(2)
col3 char(2)
Active_dt date
col5 char(2)
col6 char(2) |
The active_dt is the date when we insert the record.
I want to delete the records from this table if the Active_dt older than (1year + 1month).
I request if somebody can help me in writing the Sql for this. |
|
| Back to top |
|
 |
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
| What have you tried so far and what results have you gotten |
|
| Back to top |
|
|
alluri12
New User
Joined: 15 Aug 2006
Posts: 44
Location: Virginia, USA
|
|
|
|
I tried
delete from table1
where days(current date) - days(active_dt) > 395
I thought 1 year = 365 days
1 month = 30 days
I know this is not correct b.c days for the month changes to 30 or 31 and for the year it will 365 or 366 |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| why don't you try something along the line of current-date - 13 months > active_dt |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
You need to define exactly what 1 year is to your organization. Same with 1 month. Diferent organizations have different date rules. |
|
| Back to top |
|
|
alluri12
New User
Joined: 15 Aug 2006
Posts: 44
Location: Virginia, USA
|
|
|
|
| Thank you Dick Brenholtz for a fast respnose. It works.... |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| Quote: |
You need to define exactly what 1 year is to your organization. Same with 1 month. Diferent organizations have different date rules.
|
when we are talking about financial calculations, I agree.
but this is an archival/history thing and 13 months from the current date, is always going to be 13 months from the current date. and because it is the current date, db2 will always calculate the 'correct date' based on today's date.
but, I agree, also, that the TS should be aware of the organization's date definitions.
and I wanted to be the last poster of this thread |
|
| Back to top |
|
|
alluri12
New User
Joined: 15 Aug 2006
Posts: 44
Location: Virginia, USA
|
|
|
|
| The requirements that I got for this code is from the current date. So this code works.... I too agree differnt org has different date rules |
|
| Back to top |
|
|
alluri12
New User
Joined: 15 Aug 2006
Posts: 44
Location: Virginia, USA
|
|
|
|
I found another solution also for this requriement. It is almost same as what dick gave... but just want to share...
| Code: |
Delete From table1
WHERE DAYS(Active_dt) < DAYS((CURRENT DATE - 1 YEAR) - 1 MONTH) |
|
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| alluri12 wrote: |
I found another solution also for this requriement. It is almost same as what dick gave... but just want to share...
| Code: |
Delete From table1
WHERE DAYS(Active_dt) < DAYS((CURRENT DATE - 1 YEAR) - 1 MONTH) |
|
days(date1) < days(date2)
is like
Col1 + 1000 < Col2 +1000
it is correct but more sense and - far more important - much more performant is
date1 < date2
col1 < col2 |
|
| Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
Instead of
| Code: |
| WHERE DAYS(Active_dt) < DAYS((CURRENT DATE - 1 YEAR) - 1 MONTH) |
I think
| Code: |
WHERE Active_dt < (CURRENT DATE - 1 YEAR) - 1 MONTH
|
would be more efficient, evaluating (current date - 1 year) - 1 month would only have to be done once, but evaluating date(active_dt) would have to be done for every row before it could be tested. |
|
| Back to top |
|
|
|
|
