How to get the date of the month of last sunday dynamically?

Hello,

I want to get the last sunday's date via a SQL query.  So for example if I run this today (dec 16th 2011), it should show up 11, since Dec 11th was the most recent Sunday.  If I were to run this some time in the future, I'd still want the date of the month for the most recent Sunday.

Could you also kindly explain the logic
LVL 7
MrAliAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

DALSOMCommented:
Hi,
This works :

declare @dt datetime
 -- replace this variable with your table datetime field if you need last sunday of your date month.
set @dt = getdate()
select dateadd(dd,((DATEPART(dw,DATEADD(mm,1,@dt)) - 1)*-1),@dt)

Hope this help you! :)
0
Anthony PerkinsCommented:
This code below should be fine provided you are using a weekday starting Sunday.  In other words SELECT @@DATEFIRST returns 7 (Sunday)

DECLARE @Dat DATETIME
Set @Dat = GETDATE()
SELECT DATEADD(DAY, 1 - DATEPART(weekday, @Dat), @Dat)
0

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
Anthony PerkinsCommented:
The following should work in any language:
SELECT DATEADD(DAY, 1 - @@DATEFIRST % 7 - DATEPART(weekday, @Dat), @Dat)

If it does for you as well, I will give you the full explanation.

This is how I tested it:
DECLARE @Dat DATETIME = GETDATE()
SET LANGUAGE Italian;
SELECT DATEADD(DAY, 1 - @@DATEFIRST % 7 - DATEPART(weekday, @Dat), @Dat)
SET @Dat = '20111213'
SELECT DATEADD(DAY, 1 - @@DATEFIRST % 7 - DATEPART(weekday, @Dat), @Dat)
SET LANGUAGE us_english;
SELECT DATEADD(DAY, 1 - @@DATEFIRST % 7 - DATEPART(weekday, @Dat), @Dat)

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Anthony PerkinsCommented:
Incidentally, if you are not using SQL Server 2008 you will have to test it as follows:
DECLARE @Dat DATETIME 
Set @Dat = GETDATE()
SET LANGUAGE Italian;
SELECT DATEADD(DAY, 1 - @@DATEFIRST % 7 - DATEPART(weekday, @Dat), @Dat)
SET @Dat = '20111213'
SELECT DATEADD(DAY, 1 - @@DATEFIRST % 7 - DATEPART(weekday, @Dat), @Dat)
SET LANGUAGE us_english;
SELECT DATEADD(DAY, 1 - @@DATEFIRST % 7 - DATEPART(weekday, @Dat), @Dat)

Open in new window

0
DALSOMCommented:
Hi,
Sorry , I misplaced some sentences. I wanted to put it all togheter in just one line. Not for now,
see this :

declare @dt datetime
set @dt = GETDATE()     -- use a date.
set @dt = DATEADD(mm,1,@dt)  -- add a month.
set @dt = DATEADD(dd,day(@dt)*-1,@dt) -- look for last day of your month.
set @dt = DATEADD(dd,(DATEPART(dw,@dt) - 1)*-1,@dt) -- substract all days after the sunday.
select @dt   -- you got last month's sunday!

And in one line, seems like this :

select dateadd(dd,((datepart(dw,(DATEADD(dd,day(@dt)*-1,(DATEADD(mm,1,@dt)))))-1)*-1),DATEADD(dd,day(@dt)*-1,(DATEADD(mm,1,@dt))))


Sorry by the error. I also test for more than one month.

Hope this really help you!




0
DALSOMCommented:
Hi again,
by the way, last sunday is just line 3 of above script,

declare @dt datetime
set @dt = getdate()

select DATEADD(dd,(DATEPART(dw,@dt) - 1)*-1,@dt) -- substract all days after the sunday.

Then, there you are!

Sorry again,
This is the answer your are looking at your first post!

Dalsom
0
DALSOMCommented:
Explain :  

datepart(dw,date)  -  Extract the day of the week number : 1 for sunday / 7 for Saturday.
Then if you have a 7 (saturday) you need to substract 6, and then to substract 6 days :

you must use the fucntion dateadd(datepart,numbertosubstract, date), and the number to substract is a negative one or zero in case that your date is on sunday. And because of this you must multiply by -1 .

Please for better explain, you must read sqlserver help for functions : dateadd() and datepart().

There is your explanation, And expect win the answer! :)

Dalsom.
0
MrAliAuthor Commented:
Thank you everyone who posted,

I'm going to go through each solution and award points to the one I felt was the best suited for my question, I really appreciate the effort and time of everyone though, you all rock.
0
MrAliAuthor Commented:
Explained it well
0
Anthony PerkinsCommented:
DALSOM

You should understand the solution you provided will only function if the week starts on Sunday.  This is not true for all languages.  For example, try the following and you will see what I mean:
DECLARE @Language sysname
SET @Language = @@LANGUAGE			-- Save your current language setting:

SET LANGUAGE Italian			-- The week starts on Monday
declare @dt datetime
set @dt = getdate()
select DATEADD(dd,(DATEPART(dw,@dt) - 1)*-1,@dt)

SET LANGUAGE @Language			-- Restore your original Setting
select DATEADD(dd,(DATEPART(dw,@dt) - 1)*-1,@dt)

Open in new window

0
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.