Delete where condition does not exist Leap Year

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Delete where condition does not exist Leap Year

Author

Topic

infodemers
Posting Yak Master

183 Posts

Posted - 2012-03-02 : 09:24:38

Hi,

I have a query that delete old data in one of my table.
So lets say today's date is 2012-03-01. So I would want to delete the data that is one year old.
I would substract 1 to the year 2012 to get 2011-03-01.Then I would run the following query:

DELETE FROM table1 WHERE day_date <= '2011-03-01'

But when today's date is 2012-02-29 (leap year) I would run this:
DELETE FROM table1 WHERE day_date <= '2011-02-29'
Because that date did not exist in 2011, I receive the following error message :
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Any idea how to take care about the leap year when deleting data based on date?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-03-02 : 10:05:08

use the DATEADD command rather.



DECLARE @now DATE = '20120301'

SELECT DATEADD(DAY, -1, DATEADD(YEAR, -1, @now))

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-03-02 : 10:07:55

[code]DELETE
FROM dbo.Table1
WHERE day_date <= DATEDIFF(DAY, '19000101', DATEADD(YEAR, -1, GETDATE()))[/code]

N 56°04'39.26"
E 12°55'05.63"

infodemers
Posting Yak Master

183 Posts

Posted - 2012-03-02 : 10:22:33

First of all, Thanks Charlie and SwePeso!

Charlie, you suggestion does not give me the correct date if the declared DATE is 2012-02-29 , It gives me 2011-02-27 instead of 2011-02-28
SwePeso, your suggestion works like expected!

Regards!

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-03-02 : 11:38:54

cool. Thanks for the feedback.

Charlie.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2012-03-02 : 12:25:27

Is there a reason to avoid simply this?

DECLARE @now DATEtime
set @now = '2/29/2012'

--select DATEADD(YEAR, -1, @now)

DELETE
FROM dbo.Table1
WHERE day_date <= DATEADD(YEAR, -1, @now)

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-03-02 : 13:12:06

quote:
Originally posted by Vinnie881

Is there a reason to avoid simply this?

DECLARE @now DATEtime
set @now = '2/29/2012'

--select DATEADD(YEAR, -1, @now)

DELETE
FROM dbo.Table1
WHERE day_date <= DATEADD(YEAR, -1, @now)

Only if the time portion would be an issue.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2012-03-02 : 14:12:25

Got it, after re-reading the original post I see that he was only providing a sample of specifying the date, which would be 00:00:00, but I see now why PESO coded using getdate() which would have time on it, and makes perfect sense. It's been a long day:)

Thanks for clarifying!

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources