Delete records on the last day of the month 2

[kizziebutler]

Hello,

I have records be inserted into a table, what I would like to do is delete the records in the table on the last day of the month, I am not sure what the sql script is to find the last day of the month. Can you help?

 

[bborissov]

DECLARE @dDate AS DateTime
DECLARE @dDate1 AS DateTime
SET @dDate = '2003/02/15 00:00:00'
SET @dDate1 = DateAdd(month,1,@dDate)
print DateAdd(day,DatePart(day,@dDate1)*-1,@dDate1)

Borislav Borissov

 

[Erics44]

Hi
I am not sure if there is a function that finds that last day of a month. I think it could be easier to find the firts day of the following month and take a day off.
Thanks

 

[Erics44]

Hi
Sorry, i didnt realise you already had a reply to this.
Thanks

 

[kizziebutler]

What I would like to be able to do, is delete records older than 1 month. would it be something like month + 1.

much appreciated.

 

[vongrunt]

Older than 1 month before now, or older than first day of month?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[kizziebutler]

Older than one month, please.

 

[vongrunt]

Then this is usually good enough:

DELETE FROM myTable
WHERE dateColumn < DATEADD(mm, -1, GETDATE())

This is for anything before current month:

DECLARE @dDate datetime
SET @dDate = DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)

DELETE FROM myTable
WHERE dateColumn < @dDate -- less than beginning of current month

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)