asked on

Return various date ranges within SQL (last month, this month, etc.)

Greetings,

I am looking for how to get the following 6 date ranges:
1) This week (first day(su), last day(sa))
2) Last week (first day(su), last day(sa))
3) This month (first day, last day)
4) Last month (first day, last day)
5) This year (first day, last day)
6) Last year (first day, last day)

From another PAQ, I found this for getting item 4:
--------------------------------------------------------------------------------------------------------------
DECLARE @Today datetime, @FirstDay datetime, @LastDay datetime

SET @Today = CAST(CONVERT(nvarchar(10), GETDATE(), 120) AS datetime) -- strips time off

SET @LastDay = DATEADD(day, - DAY(@Today), @Today) -- last day of previous month
SET @FirstDay = DATEADD(month, -1, DATEADD(day, 1, @LastDay)) -- first day of previous month
--------------------------------------------------------------------------------------------------------------

This works great for Last month. I just don't know how to manipulate it to get the other 5 ranges I need.

Thanks in advance!
chris.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

Let me know if, for the last dates, you want the time to be "23:59:59.997" rather than 00:00:00.000 (midnight), for example, if you are using these values in date range checks.

cciesliga

ASKER

Those look great so far. What about first/last for previous year?

cciesliga

ASKER

Disregard previous year, I got that bit figured out.

Thanks again for your help ScottPletcher!

kennmurrah

From another question, I found the following code to select the records from last month. I've been struggling to modify it to show the records from two months ago, or three. (For instance, it's February now, so this code selects January records ... I need to select December, or November, or ... ) Can someone help?

Anyway, here's the code that works beautifully for last month ...

DECLARE @Today datetime, @FirstDay datetime, @LastDay datetime

SET @Today = CAST(CONVERT(nvarchar(10), GETDATE(), 120) AS datetime) -- strips time off

SET @LastDay = DATEADD(day, - DAY(@Today), @Today) -- last day of previous month
SET @FirstDay = DATEADD(month, -1, DATEADD(day, 1, @LastDay)) -- first day of previous month

Scott Pletcher

--for Dec, for example:

SET @LastDay = '20081231'
SET @FirstDay = '20081201'