-1

I want to get only the start date of month between selected date

ex : 

start date = 2012 -05-01
end date = 2013-04-01

I want like this 

   Dates :
   2012 -05-01
   2012 -06-01
   2012 -07-01 .....
   2013 -01-01 .....
   2012 -05-01
Kermit
  • 33,827
  • 13
  • 85
  • 121
Kshitiz
  • 35
  • 2
  • 5
  • Check this out: http://stackoverflow.com/questions/1520789/how-can-i-select-the-first-day-of-a-month-in-sql – Jason Carter Apr 09 '13 at 13:49
  • This question does not show any research effort. It is important to **do your homework**. Tell us what you found and ***why*** it didn't meet your needs. This demonstrates that you've taken the time to try to help yourself, it saves us from reiterating obvious answers, and most of all it helps you get a more specific and relevant answer. [FAQ](http://stackoverflow.com/questions/how-to-ask). – Kermit Apr 09 '13 at 13:49

2 Answers2

1

CTE will do the trick

   Declare @beginDt SmallDatetime = '13 Jan 2012'
   Declare @endDt SmallDatetime = '2 Apr 2013';
   With MonthList(aMon)
      As
      (Select DateAdd(month, datediff(month, 0, @beginDt), 31)
         Union All
       Select DateAdd(month, 1, aMon) From MonthList
       Where DateAdd(month, 1, aMon) < @endDt)
       Select aMon from MonthList
Charles Bretana
  • 143,358
  • 22
  • 150
  • 216
0

First, create a calendar table. Then you can write a simple query:

select [Date]
from dbo.Calendar
where IsFirstDayOfMonth = 1 and [Date] between '20120501' and '20130401'

This query is much clearer than using using a function-based solution and a calendar table has many other uses too.

Community
  • 1
  • 1
Pondlife
  • 15,992
  • 6
  • 37
  • 51