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?
 
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
 
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:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.