Calculate last 6 month month end date

Question

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

crazy4sql SSCoach Points: 19590 More actions · Answer 1

print getdate()

print dateadd(m,-1,getdate())

print dateadd(m,-2,getdate())

.

and so on.

----------
Ashish

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

One simple way: -

DECLARE @date DATETIME, @number INT

SET @date = '2011-06-01 00:00:00'

SET @number = 6

;WITH CTE AS (

SELECT @number-1 AS months

UNION ALL

SELECT months - 1

FROM CTE

WHERE months > 0)

SELECT DATEADD(DAY,-1,DATEADD(MONTH,-months,DATEADD(DAY,-(DAY(@date)-1),@date)))

FROM CTE

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

nigel. SSChampion Points: 11636 More actions · Answer 3

Or

SELECT

TOP 6 DATEADD(DAY,-1,DATEADD(MONTH,1-ROW_NUMBER() OVER(ORDER BY name) ,'Jun 2011'))

FROM

sys.columns