November 11, 2011 at 7:50 am
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
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
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
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
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
November 11, 2011 at 9:12 am
YES, MANY THANKS!!!!!:-)
November 11, 2011 at 9:21 am
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
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
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
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
November 11, 2011 at 9:53 am
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