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.
LVL 1
cciesligaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
DECLARE @today DATETIME
DECLARE @datefirst TINYINT

SET @today = CONVERT(CHAR(8), GETDATE(), 112) --'Jun 26 2005'
SET @datefirst = @@DATEFIRST
SET DATEFIRST 7

SELECT DATEADD(DAY, -(DATEPART(WEEKDAY, @today)) + 1, @today) AS [First Day of This Week]
SELECT DATEADD(DAY, -(DATEPART(WEEKDAY, @today)) + 7, @today) AS [Last Day of This Week]
SELECT DATEADD(DAY, -DATEPART(DAY, @today) + 1, @today) AS [First Day of This Month]
SELECT DATEADD(DAY, -DATEPART(DAY, @today), DATEADD(MONTH, 1, @today)) AS [Last Day of This Month]
SELECT DATEADD(MONTH, -1, DATEADD(DAY, - DAY(@Today) + 1, @Today)) AS [First Day of Previous Month]
SELECT DATEADD(DAY, - DAY(@Today), @Today) AS [Last Day of Previous Month]
SELECT DATEADD(DAY, -DATEPART(DAYOFYEAR, @today) + 1, @today) AS [First Day of This Year]
SELECT CAST(CAST(YEAR(@today) AS CHAR(4)) + '1231' AS DATETIME) AS [Last Day of This Year]
SET DATEFIRST @datefirst

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
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.
cciesligaAuthor Commented:
Those look great so far.  What about first/last for previous year?
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

cciesligaAuthor Commented:
Disregard previous year, I got that bit figured out.

Thanks again for your help ScottPletcher!
kennmurrahCommented:
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 PletcherSenior DBACommented:
--for Dec, for example:

SET @LastDay = '20081231'
SET @FirstDay = '20081201'
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.