Post reply

First sunday prefious month - 6 days

  • November 11, 2011 at 7:50 am

    #247673

    I need help in creating a script that will give me the date of the First Sunday in the PREVIOUS month less 6 days.

    Thanks for any help that you can provide.

  • November 11, 2011 at 8:06 am

    #1406293

    select DT = dateadd(dd,-6,dateadd(dd,((datediff(dd,-53684,dateadd(mm,datediff(mm,0,getdate())-1,6)+7)/7)*7)-7,-53684))

    Results:

    DT

    -----------------------

    2011-09-26 00:00:00.000

  • November 11, 2011 at 8:39 am

    #1406336

    Thank you for your post. It works great. I also need the first Monday of the month - 6 days. How would I modify this script to get that?

  • November 11, 2011 at 8:43 am

    #1406343

    Sorry, I meant first Monday of previous month - 6 days. Would I just change the 53684 to 53685?

    I really apppreciate your help!

  • November 11, 2011 at 9:04 am

    #1406361

    Is this what you're after?

    SELECT DATEADD(D, -6, DATEADD(D, DATEDIFF(D, 6, DATEADD(M, 12 * DATEPART(YY, GETDATE()) + DATEPART(M, DATEADD(M, -1, GETDATE())) - 22801, 6)) / 7 * 7, 6)) AS firstSunday_minus_6,

    DATEADD(D, -6, DATEADD(D, DATEDIFF(D, 7, DATEADD(M, 12 * DATEPART(YY, GETDATE()) + DATEPART(M, DATEADD(M, -1, GETDATE())) - 22801, 6)) / 7 * 7, 7)) AS firstMonday_minus_6,

    DATEADD(D, -6, DATEADD(D, DATEDIFF(D, 8, DATEADD(M, 12 * DATEPART(YY, GETDATE()) + DATEPART(M, DATEADD(M, -1, GETDATE())) - 22801, 6)) / 7 * 7, 8)) AS firstTuesday_minus_6,

    DATEADD(D, -6, DATEADD(D, DATEDIFF(D, 9, DATEADD(M, 12 * DATEPART(YY, GETDATE()) + DATEPART(M, DATEADD(M, -1, GETDATE())) - 22801, 6)) / 7 * 7, 9)) AS firstWednesday_minus_6,

    DATEADD(D, -6, DATEADD(D, DATEDIFF(D, 10, DATEADD(M, 12 * DATEPART(YY, GETDATE()) + DATEPART(M, DATEADD(M, -1, GETDATE())) - 22801, 6)) / 7 * 7, 10)) AS firstThursday_minus_6,

    DATEADD(D, -6, DATEADD(D, DATEDIFF(D, 11, DATEADD(M, 12 * DATEPART(YY, GETDATE()) + DATEPART(M, DATEADD(M, -1, GETDATE())) - 22801, 6)) / 7 * 7, 11)) AS firstFriday_minus_6,

    DATEADD(D, -6, DATEADD(D, DATEDIFF(D, 12, DATEADD(M, 12 * DATEPART(YY, GETDATE()) + DATEPART(M, DATEADD(M, -1, GETDATE())) - 22801, 6)) / 7 * 7, 12)) AS firstSaturday_minus_6

    SELECT DATEADD(D, DATEDIFF(D, 6, DATEADD(M, 12 * DATEPART(YY, GETDATE()) + DATEPART(M, DATEADD(M, -1, GETDATE())) - 22801, 6)) / 7 * 7, 6) AS firstSunday,

    DATEADD(D, DATEDIFF(D, 7, DATEADD(M, 12 * DATEPART(YY, GETDATE()) + DATEPART(M, DATEADD(M, -1, GETDATE())) - 22801, 6)) / 7 * 7, 7) AS firstMonday,

    DATEADD(D, DATEDIFF(D, 8, DATEADD(M, 12 * DATEPART(YY, GETDATE()) + DATEPART(M, DATEADD(M, -1, GETDATE())) - 22801, 6)) / 7 * 7, 8) AS firstTuesday,

    DATEADD(D, DATEDIFF(D, 9, DATEADD(M, 12 * DATEPART(YY, GETDATE()) + DATEPART(M, DATEADD(M, -1, GETDATE())) - 22801, 6)) / 7 * 7, 9) AS firstWednesday,

    DATEADD(D, DATEDIFF(D, 10, DATEADD(M, 12 * DATEPART(YY, GETDATE()) + DATEPART(M, DATEADD(M, -1, GETDATE())) - 22801, 6)) / 7 * 7, 10) AS firstThursday,

    DATEADD(D, DATEDIFF(D, 11, DATEADD(M, 12 * DATEPART(YY, GETDATE()) + DATEPART(M, DATEADD(M, -1, GETDATE())) - 22801, 6)) / 7 * 7, 11) AS firstFriday,

    DATEADD(D, DATEDIFF(D, 12, DATEADD(M, 12 * DATEPART(YY, GETDATE()) + DATEPART(M, DATEADD(M, -1, GETDATE())) - 22801, 6)) / 7 * 7, 12) AS firstSaturday

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • November 11, 2011 at 9:12 am

    #1406371

    YES, MANY THANKS!!!!!:-)

  • November 11, 2011 at 9:21 am

    #1406382

    I know you got an answer, but I wouldn't mind if you provided an example of what you are looking for, say based on todays date of 11/11/11?

  • November 11, 2011 at 9:24 am

    #1406386

    Lynn Pettis (11/11/2011)

    I know you got an answer, but I wouldn't mind if you provided an example of what you are looking for, say based on todays date of 11/11/11?

    Novel idea - I'm curious too

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • November 11, 2011 at 9:45 am

    #1406425

    I am working with 2 payrolls that end on Saturday and Sundays. I need to report the hours for the first weekending date of the previous month. For Sunday last month that would be 10-02-2011 and the first day of the pay week would be 9-26-2011.

    for Saturday last month that would be 10-01-2011 and the first day of the pay week would be 9-25-2011.

    I hope that explains why I needed this code and the code above works GREAT! I appreciate all the help!

  • November 11, 2011 at 9:51 am

    #1406436

    jbrewer (11/11/2011)

    I am working with 2 payrolls that end on Saturday and Sundays. I need to report the hours for the first weekending date of the previous month. For Sunday last month that would be 10-02-2011 and the first day of the pay week would be 9-26-2011.

    for Saturday last month that would be 10-01-2011 and the first day of the pay week would be 9-25-2011.

    I hope that explains why I needed this code and the code above works GREAT! I appreciate all the help!

    Hope you're still monitoring this post, I spotted a bug that will bite you early next year 🙂

    Here's the corrected code snippet.

    SELECT DATEADD(D, -6, DATEADD(D, DATEDIFF(D, 6, DATEADD(M, 12 * DATEPART(YY, DATEADD(M, -1, GETDATE())) + DATEPART(M, DATEADD(M, -1, GETDATE())) - 22801, 6)) / 7 * 7, 6)) AS firstSunday_minus_6,

    DATEADD(D, -6, DATEADD(D, DATEDIFF(D, 7, DATEADD(M, 12 * DATEPART(YY, DATEADD(M, -1, GETDATE())) + DATEPART(M, DATEADD(M, -1, GETDATE())) - 22801, 6)) / 7 * 7, 7)) AS firstMonday_minus_6,

    DATEADD(D, -6, DATEADD(D, DATEDIFF(D, 8, DATEADD(M, 12 * DATEPART(YY, DATEADD(M, -1, GETDATE())) + DATEPART(M, DATEADD(M, -1, GETDATE())) - 22801, 6)) / 7 * 7, 8)) AS firstTuesday_minus_6,

    DATEADD(D, -6, DATEADD(D, DATEDIFF(D, 9, DATEADD(M, 12 * DATEPART(YY, DATEADD(M, -1, GETDATE())) + DATEPART(M, DATEADD(M, -1, GETDATE())) - 22801, 6)) / 7 * 7, 9)) AS firstWednesday_minus_6,

    DATEADD(D, -6, DATEADD(D, DATEDIFF(D, 10, DATEADD(M, 12 * DATEPART(YY, DATEADD(M, -1, GETDATE())) + DATEPART(M, DATEADD(M, -1, GETDATE())) - 22801, 6)) / 7 * 7, 10)) AS firstThursday_minus_6,

    DATEADD(D, -6, DATEADD(D, DATEDIFF(D, 11, DATEADD(M, 12 * DATEPART(YY, DATEADD(M, -1, GETDATE())) + DATEPART(M, DATEADD(M, -1, GETDATE())) - 22801, 6)) / 7 * 7, 11)) AS firstFriday_minus_6,

    DATEADD(D, -6, DATEADD(D, DATEDIFF(D, 12, DATEADD(M, 12 * DATEPART(YY, DATEADD(M, -1, GETDATE())) + DATEPART(M, DATEADD(M, -1, GETDATE())) - 22801, 6)) / 7 * 7, 12)) AS firstSaturday_minus_6

    SELECT DATEADD(D, DATEDIFF(D, 6, DATEADD(M, 12 * DATEPART(YY, DATEADD(M, -1, GETDATE())) + DATEPART(M, DATEADD(M, -1, GETDATE())) - 22801, 6)) / 7 * 7, 6) AS firstSunday,

    DATEADD(D, DATEDIFF(D, 7, DATEADD(M, 12 * DATEPART(YY, DATEADD(M, -1, GETDATE())) + DATEPART(M, DATEADD(M, -1, GETDATE())) - 22801, 6)) / 7 * 7, 7) AS firstMonday,

    DATEADD(D, DATEDIFF(D, 8, DATEADD(M, 12 * DATEPART(YY, DATEADD(M, -1, GETDATE())) + DATEPART(M, DATEADD(M, -1, GETDATE())) - 22801, 6)) / 7 * 7, 8) AS firstTuesday,

    DATEADD(D, DATEDIFF(D, 9, DATEADD(M, 12 * DATEPART(YY, DATEADD(M, -1, GETDATE())) + DATEPART(M, DATEADD(M, -1, GETDATE())) - 22801, 6)) / 7 * 7, 9) AS firstWednesday,

    DATEADD(D, DATEDIFF(D, 10, DATEADD(M, 12 * DATEPART(YY, DATEADD(M, -1, GETDATE())) + DATEPART(M, DATEADD(M, -1, GETDATE())) - 22801, 6)) / 7 * 7, 10) AS firstThursday,

    DATEADD(D, DATEDIFF(D, 11, DATEADD(M, 12 * DATEPART(YY, DATEADD(M, -1, GETDATE())) + DATEPART(M, DATEADD(M, -1, GETDATE())) - 22801, 6)) / 7 * 7, 11) AS firstFriday,

    DATEADD(D, DATEDIFF(D, 12, DATEADD(M, 12 * DATEPART(YY, DATEADD(M, -1, GETDATE())) + DATEPART(M, DATEADD(M, -1, GETDATE())) - 22801, 6)) / 7 * 7, 12) AS firstSaturday

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • November 11, 2011 at 9:53 am

    #1406441

    Thank you very much for all of your help. 🙂

Viewing 11 posts - 1 through 10 (of 10 total)

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