Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Select all months bewtween two dates Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, April 16, 2014 4:32 AM
 SSC Eights! Group: General Forum Members Last Login: Tuesday, March 24, 2015 8:26 AM Points: 963, Visits: 1,654
 feroz_tt (4/16/2014)Hi All,I'm getting some problem here;with Numbers (Number) as (select row_number() over (order by object_id) from sys.all_objects)select dateadd(month, number, '2014-09-01')from Numberswhere number <= datediff(month, '2014-09-01', '2016-03-31')Low no of months.Select datediff(month, '2014-09-01', '2016-03-31')it should give 31 instead of 18.I don't understand where i'm wrongHow did you calculate 31?
Post #1562225
 Posted Saturday, April 19, 2014 3:46 PM
 SSC-Dedicated Group: General Forum Members Last Login: Yesterday @ 8:07 PM Points: 36,857, Visits: 33,686
 feroz_tt (4/16/2014)Hi All,I'm getting some problem here;with Numbers (Number) as (select row_number() over (order by object_id) from sys.all_objects)select dateadd(month, number, '2014-09-01')from Numberswhere number <= datediff(month, '2014-09-01', '2016-03-31')Low no of months.Select datediff(month, '2014-09-01', '2016-03-31')it should give 31 instead of 18.I don't understand where i'm wrongI'm not sure why you think there should be "31". I can understand why someone would insist that the correct answer is "19" but not "31".`with Numbers (Number) as(select row_number() over (order by object_id) -1from sys.all_objects)select Number+1,dateadd(month, number, '2014-09-01')from Numberswhere number <= datediff(month, '2014-09-01', '2016-03-31');`Results:` -------------------- -----------------------1 2014-09-01 00:00:00.0002 2014-10-01 00:00:00.0003 2014-11-01 00:00:00.0004 2014-12-01 00:00:00.0005 2015-01-01 00:00:00.0006 2015-02-01 00:00:00.0007 2015-03-01 00:00:00.0008 2015-04-01 00:00:00.0009 2015-05-01 00:00:00.00010 2015-06-01 00:00:00.00011 2015-07-01 00:00:00.00012 2015-08-01 00:00:00.00013 2015-09-01 00:00:00.00014 2015-10-01 00:00:00.00015 2015-11-01 00:00:00.00016 2015-12-01 00:00:00.00017 2016-01-01 00:00:00.00018 2016-02-01 00:00:00.00019 2016-03-01 00:00:00.000(19 row(s) affected)` --Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #1563295

 Permissions