# Get date of last Sunday of every month

• catriona_brown

Points: 435

How can I retrieve the date of the last Sunday of every month?

Thanks

• Steve Jones - SSC Editor

SSC Guru

Points: 717381

datepart will get you sundays, check BOL for the parameters.

The idea is that you want to develop an algorithm that can determine what the last Sunday is by examining various cases and trying to build a calculation.

This sounds like a test or exam question, so I don't want to give an answer straight away. I'd prefer you try to solve it and show some effort.

• catriona_brown

Points: 435

no, not an exam question just looking for a bit of direction.

• Matt Miller (4)

SSC Guru

Points: 124200

Well - if direction is what you're looking for - break the problem into two. First - determine the last day of the month. Once you have the last day of the month, you can figure out what day of the week it falls on, which will give you how many days you might need to adjust backwards from there to find the Sunday.

Try to take a stab at it from that perspective, and post a followup if you get stuck.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

• Steve Jones - SSC Editor

SSC Guru

Points: 717381

Walk through some logic. How do you figure out what the last Sunday is? (besides looking at the calendar).

There's a skill you need to build here to examine what determines the last Sunday? Is it the last week of the month? Is it one of the last 7 days of the month?

Work through it.

• Lynn Pettis

SSC Guru

Points: 442208

Just so you know; I have found an algorithm that works, and based on my testing, will work no matter the setting of DATEFIRST.

(That is a hint, and goes with the others given earlier).

If you gete stumped, let us know what you have tried so far, and we will try to give you some more hints.

😎

• catriona_brown

Points: 435

I figured it out. An no still not an exam question.

• Lynn Pettis

SSC Guru

Points: 442208

At this point, since you figured it out, is when you'd post what you did to answer your own question. One reason, others may have a similiar question, but also to see if others have another way of doing the same thing.

How you solved the issue may be different from the way I may have solved. Is one way better than another, sometimes yes and sometimes no. The key is discovering more than one way to do things.

😎

• Jeff Moden

SSC Guru

Points: 995525

Ok... now that everyone has figured stuff out... how 'bout everyone post their solutions? 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Christopher Stobbs

SSC-Insane

Points: 21098

Good call Jeff,

This may not work if the DateFirst changes

still working on that part.

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life
• Lynn Pettis

SSC Guru

Points: 442208

Jeff, I will post my solution as soon as I get to work. My code is sitting there, but I'm still at home getting ready to leave. See you in this thread again in about 30.

😎

• Matt Miller (4)

SSC Guru

Points: 124200

All right - here's my entry, with some data to test it with:

``` --test data declare @date datetime set @date='01/01/2008'; select top 500 @date+n as date into #tblDate from tally where n<501 --last sunday of the month calc. Uses a known sunday declare @firstSunday datetime; set @firstSunday=6; select date,dateadd(week,datediff(week,@firstSunday,dateadd(month,datediff(month,0,date)+1,0)-1 ),@firstSunday) from #tbldate order by date ```

Having been bitten by the DATEFIRST thing before - I just use known dates to avoid those shenanigans.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

• Lynn Pettis

SSC Guru

Points: 442208

Here is my solution:

``` declare @TheDate datetime; set @TheDate = '2008-08-15'; select @TheDate, dateadd(mm, datediff(mm,0,@TheDate) + 1, 0) - (datepart(dw,dateadd(mm, datediff(mm,0,@TheDate) + 1, 0)) + case when @@datefirst - 1 = 0 then 7 else @@datefirst - 1 end) % 7; ```

• Jeff Moden

SSC Guru

Points: 995525

Matt Miller (6/12/2008)

All right - here's my entry, with some data to test it with:

``` --test data declare @date datetime set @date='01/01/2008'; select top 500 @date+n as date into #tblDate from tally where n<501 --last sunday of the month calc. Uses a known sunday declare @firstSunday datetime; set @firstSunday=6; select date,dateadd(week,datediff(week,@firstSunday,dateadd(month,datediff(month,0,date)+1,0)-1 ),@firstSunday) from #tbldate order by date ```

Having been bitten by the DATEFIRST thing before - I just use known dates to avoid those shenanigans.

I think your @firstSunday should be a 7, no? Right now, you code lists Saturdays... of course that also means your code will (should) work for any day of the week...:D

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Jeff Moden

SSC Guru

Points: 995525

Jeff Moden (6/12/2008)

I think your @firstSunday should be a 7, no? Right now, you code lists Saturdays... of course that also means your code will (should) work for any day of the week...:D

Wait a minute... I gotta get the shoelaces out of my mouth, first... I was looking at 2008 and you're generating for 2009. My mistake.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."