This seems to be a frequently asked question that people give multiple answers to. Even I struggled with something so simple. Why?? I think we all too often try to think out of the box when often times the box holds the answer.
GETTING THE FIRST DAY OF THE MONTH
Subtract the day on the month +1 from the date. Simple huh?
DATEADD(d,-DAY(getdate())+1,getdate())
GETTING THE LAST DAY OF THE MONTH To get the last day of the month you simply add month and subtract day from the above code. ** If you are using time then you would subtract some derivative of time such as second(s) or minute(s). **
We would do something like the following:
DATEADD(d,-1,DATEADD(m,1,DATEADD(d,-DAY(getdate())+1,getdate())))
Simple sweet and not complicated
UPDATE - 10.4.2012
Since the release of SQL 2012 (Denali) finding the last/first day of the month becomes easier. With 2012 there is a new call to find the end of the month EOMonth(date). This will return the end of the month for the given date. With a little imagination I imagine you can have a lot of fun with this new function.
