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
 General SQL Server Forums
 New to SQL Server Programming
 all months of 3 years in sql

Author  Topic 

daliri
Starting Member

2 Posts

Posted - 2012-11-06 : 03:21:06
I want to show all month of 3 different years in sql , how can H do it?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-06 : 06:48:31
Something like this?
DECLARE @dt DATETIME = '20120111';

;WITH N(n) AS
(SELECT 1 UNION ALL SELECT n+1 FROM N WHERE n < 36)
SELECT
DATEADD(mm,DATEDIFF(mm,0,@dt)-n,0)
FROM
N;
Go to Top of Page

Mike Jackson
Starting Member

37 Posts

Posted - 2012-11-06 : 08:26:22
You might also want to look into a calendar table.

Mike
Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2012-11-11 : 18:33:42
quote:
Originally posted by sunitabeck

Something like this?
DECLARE @dt DATETIME = '20120111';

;WITH N(n) AS
(SELECT 1 UNION ALL SELECT n+1 FROM N WHERE n < 36)
SELECT
DATEADD(mm,DATEDIFF(mm,0,@dt)-n,0)
FROM
N;




Oh, be careful, Sunita. That's a "counting Recursive CTE". Please see the following article for why you shouldn't do such a thing even for such a small number of rows.
[url]http://www.sqlservercentral.com/articles/T-SQL/74118/[/url]


--Jeff Moden
Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2012-11-11 : 18:36:36
quote:
Originally posted by daliri

I want to show all month of 3 different years in sql , how can H do it?



When do you want the year to start? January? Or do you want to show the next 3 years from today, previous 3 years, or ???


--Jeff Moden
Go to Top of Page
   

- Advertisement -