CODEWITHSUNDEEP
sql
user2965112
user2965112

Reputation: 97

SQL Calculating full months

I currently have an sql stored procedure which closes open tickets automatically after 6 months. Something like the following

UPDATE TABLE.TICKETS
SET STATUS = 'Closed'
WHERE DateAdd(MM,6,OPENED_DATE) < GETDATE()

This sets the status to closed whenever the opened_date was exactly 6 months ago or more.

What I need it to do is for it to calculate full months. For example

If the opened date is 05/05/2010, I don't want it to close at 05/11/2010 which it currently does (exactly 6 months - I now want it to close at the END of the month which is 6 months away. i.e the 05/05/2010 ticket would close on 01/12/2010. A ticket lodged on 15/05/2010 would also close on 01/12/2010.

Can anyone advise on the best way to do this? My mind is drawing a blank :(. Thanks

Upvotes: 0

Views: 85

Answers (2)

hagope
hagope

Reputation: 5531

Check the last day of the month of the OPEN_DATE against today:

UPDATE TABLE.TICKETS
SET STATUS = 'Closed'
WHERE DateAdd(MM,6,DATEDIFF(m,0,OPENED_DATE)+1,0))) < GETDATE()

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271031

How about this:

UPDATE TABLE.TICKETS
SET STATUS = 'Closed'
WHERE OPENED_DATE < dateadd(mm, -6, GETDATE() - day(getdate) + 1)

The day() function returns the day of the month, so the expression GETDATE() - day(getdate) + 1 returns the first day of the current month. The rest subtracts six months and compares that to the OPENED_DATE.

Note that I switched the logic from the column to the "constant". This allows SQL Server to use an index on OPENED_DATE if one is available (the technical term for this is "sargability").

Upvotes: 2

Related Questions