Months Remaining in Year

Question

Post reply

Months Remaining in Year

Laura McHugh

Old Hand

Points: 308
More actions
September 24, 2009 at 7:35 am

#385425

Hi,
I am looking for dynamic SQL to return the current month and the months left in the year dependant on the current month. So if I ran it today I would want to return Sept through to Dec.
The results would look like this
9/1/2009
10/1/2009
11/1/2009
12/1/2009
Any help would be appreciated

Viewing 6 posts - 1 through 6 (of 6 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

This should do the trick:

;WITH Months (firstDayOfMonth)

AS (

SELECT TOP 12

CAST(

CAST(YEAR(GETDATE()) AS char(4)) +

RIGHT('0' + CAST(ROW_NUMBER() OVER (ORDER BY object_id) AS varchar(2)),2) +

'01'

AS datetime)

FROM master.sys.all_columns

)

SELECT firstDayOfMonth

FROM Months

WHERE MONTH(firstDayOfMonth) >= month(getdate())

Regards

Gianluca

-- Gianluca Sartori

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 2

Here is another way of doing so:

;with Months as (

select DATEADD(mm,datediff(mm,'19000101',getdate()),'19000101') as MyDate

union all

select DATEADD(mm,1,MyDate) from Months where Month(MyDate) < 12)

select * from Months

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Laura McHugh Old Hand Points: 308 More actions · Answer 3

Thanks that is perfect! ould this code be adapted to run for x number of years also or is it restrained to just one year.

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 4

You can modify my code to show more then one year. Here is an example:

declare @StopDate datetime

set @StopDate = '20110101'

;with Months as (

select DATEADD(mm,datediff(mm,'19000101',getdate()),'19000101') as MyDate

union all

select DATEADD(mm,1,MyDate) from Months where MyDate < @StopDate)

select * from Months

where MyDate < @StopDate

order by MyDate

Remember that if you’ll run the recursive more then 100 times, you’ll get a run time error. To provent that you’ll might need to use the maxrecursion option

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 5

A recursive CTE is not necessary for this -- a Tally (Numbers) table can do the job more efficiently.

The following uses the built-in numbers table: master.dbo.spt_values, but if you need to return more than 256 rows, use a custom tally table instead. The following works on SQL Server 2000 as well as SQL Server 2005.

DECLARE @EndYear int

SELECT @EndYear = 2010

SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()) + N.number, 0)

FROM master.dbo.spt_values N

WHERE (N.type = 'P' AND N.number <= 12 * (1 + COALESCE(@EndYear - YEAR(GETDATE()), 0)) - MONTH(GETDATE()))

If you just need to return months to the end of the current year then the query simplifies to:

SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()) + N.number, 0)

FROM master.dbo.spt_values N

WHERE (N.type = 'P' AND N.number <= 12 - MONTH(GETDATE()))