CODEWITHSUNDEEP
sqlsql-serversql-server-2005
user380432
user380432

Reputation: 4779

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

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.

Upvotes: 3

Views: 542

Answers (1)

Rob
Rob

Reputation: 45779

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

Upvotes: 6

Related Questions