## Select all months bewtween two dates

 Author Message twin.devil Ten Centuries Group: General Forum Members Points: 1376 Visits: 2596 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? Jeff Moden SSC-Forever Group: General Forum Members Points: 45002 Visits: 39880 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 ModenRBAR 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.Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.Although change is inevitable, change for the better is not.Just because you can do something in PowerShell, doesnt mean you should. Helpful Links:How to post code problemsHow to post performance problemsForum FAQs