SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 all months of 3 years in sql
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

daliri
Starting Member

Iran
2 Posts

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

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/06/2012 :  06:48:31  Show Profile  Reply with Quote
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 - 11/06/2012 :  08:26:22  Show Profile  Reply with Quote
You might also want to look into a calendar table.

Mike
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
651 Posts

Posted - 11/11/2012 :  18:33:42  Show Profile  Reply with Quote
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.
http://www.sqlservercentral.com/articles/T-SQL/74118/


--Jeff Moden
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
651 Posts

Posted - 11/11/2012 :  18:36:36  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000