Get date of last Sunday of every month

  • catriona_brown

    SSC-Addicted

    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

    SSC-Addicted

    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

    SSC-Addicted

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

    Helpful Links:
    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

    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: 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."

    Helpful Links:
    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."

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

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

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