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.

Think about this, the enddate is always plus 1 month - 1 day — HLGEM, Aug 26 '10 at 13:57
@Mitch, I did do a quick search around but bizarrely couldn't find anything. I'm as surprised as anyone that this question didn't already exist, unless my search powers are failing me! ;=) — Rob, Aug 26 '10 at 14:33
http://stackoverflow.com/questions/1051488/get-the-last-day-of-the-month-in-sql — Mitch Wheat, Aug 26 '10 at 14:46
http://stackoverflow.com/questions/1520789/how-can-i-select-the-first-day-of-a-month-in-sql — Mitch Wheat, Aug 26 '10 at 14:46

score 6 · Accepted Answer · answered Aug 26 '10 at 13:58

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

If you wanted to use current date. SET @StartDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) — etliens, Aug 26 '10 at 17:34

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

1 Answers1