Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
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.
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;
Mike Jackson
Starting Member
37 Posts
Posted - 2012-11-06 : 08:26:22
You might also want to look into a calendar table.Mike
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
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