Get date of last Sunday of every month

Question

Get date of last Sunday of every month

catriona_brown

SSC-Addicted

Points: 435
More actions
June 11, 2008 at 8:37 am

#192398

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

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 1

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 SSC-Addicted Points: 435 More actions · Answer 2

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

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: 734449 More actions · Answer 4

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: 442467 More actions · Answer 5

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 SSC-Addicted Points: 435 More actions · Answer 6

catriona_brown

SSC-Addicted

Points: 435

June 12, 2008 at 2:15 am

#827691

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

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: 1003863 More actions · Answer 8

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.

Change is inevitable... Change for the better is not.

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

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 9

Good call Jeff,

This may not work if the DateFirst changes

SELECT DATEADD(DAY,1-DATEPART(weekday,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,@dte)+1, 0))),dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,@dte)+1, 0)))

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: 442467 More actions · Answer 10

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: 124210 More actions · Answer 11

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: 442467 More actions · Answer 12

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: 1003863 More actions · Answer 13

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.

Change is inevitable... Change for the better is not.

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 14

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.

Change is inevitable... Change for the better is not.

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