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

Question

I have an @StartDate and @EndDate.

I need the @StartDate to be the day the query is ran(which will always be the first of the month) and the @EndDate to be exaclty at the end of the month no matter if the month is 30 or 31 days, etc.

Answer 1

A worked 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 (that's what the DATEADD is doing), and then deduct one day.

The output from that query is:

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