## Select all months bewtween two dates

 Author Message twin.devil SSChampion Group: General Forum Members Points: 12316 Visits: 2898 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 Guru Group: General Forum Members Points: 508169 Visits: 44280 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.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs