SELECT DATEADD( mm, DATEDIFF( mm, 0, GETDATE() ), 0 ) AS FirstOfMonth
Since I always manage to get the syntax wrong from memory, above is a SELECT statement that will return the first day of the current month.
Edit: As usual, Joshua has a good point. Also, it just goes to show that re-inventing the wheel every time, instead of re-using code, isn’t the way to go.
“Well I wouldn’t have done it that way.” – Someone to be named later
Try this instead for a more efficient solution:
SELECT
GETDATE() AS CurrentDate
, DATEDIFF(mm,0,GETDATE()) AS NumberOfMonthsSince
, DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) AS FirstOfMonth
, DateAdd(dd,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) AS EndOfPreviousMonth
, DateAdd(ms,-3,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) AS EndOfPreviousMonth_Exact
The CONVERT(VARCHAR(10) part is good if you want to output just the date without the time as text. But a bit expensive to force two type conversions when the same trick with DATEADD and DATEDIFF can work without the type conversions.