SQL 1st of the month to the end of the month

Question

I have @StartDate and @EndDate.

I need @StartDate to be the day of the request (which will always be the first this month), and @EndDate to be exaccty at the end of the month, whether the month is 30 or 31 days, etc.

Answer 1

Produced example:

DECLARE @StartDate DATETIME, @EndDate DATETIME

SET @StartDate = '2010-01-01 00:00:00.000'
SET @EndDate = DATEADD(m, 1, @StartDate)

SELECT @StartDate, @EndDate - 1

Basically you want to take the start date, add one month (which does DATEADD), and then subtract one day.

The result of this query:

StartOfMonth            EndOfMonth
----------------------- -----------------------
2010-01-01 00:00:00.000 2010-01-31 00:00:00.000