Find the Next Arbitrary Day of the Week in T-SQL

I’m pretty proud of this little beastie. I’m also pretty sure that it can be reduced a bit, but not right now. Fire up your T-SQL enviroments!

DECLARE @desiredDate datetime
SET @desiredDayOfWeek = 4 --& Let's find, oh, Tuesdays. I like Tuesdays, usually.
SET @desiredDate = DATEADD(dd, ((DATEPART(ww, DATEADD(ww, 1, GETDATE())) - DATEPART(ww, DATEADD(dd, 7%((@desiredDayOfWeek - DATEPART(dw, GETDATE())) + 7), GETDATE()))) * 7) + (-1 * (DATEPART(dw, GETDATE()) - @desiredDayOfWeek)), GETDATE())

Whee!

January 26th, 2006 | Computer, Microsoft, Technology

4 comments

Ryan, send me your freakin’ paper, send me some else’s competent summary of the thing, or shut up about the damn slingshot!

Chuck Norris roundhouse kicks people who keep talking about their slingshot.

Comment by Noel — Friday, January 27, 2006 @ 5:10 pm

Jeff, you’re right of course, about both points. The code in my post was old, and the production version differs slightly, but it still suffers from not being able to work with days that have already past in the week (though in production, there’s a guarentee that it will never have to deal with that case). Your solution is still more correct and elegant. I had played with using a modulo, but couldn’t get the 6+…+1 constants right. Now I’ll have to test and update that production code…

This is why I enjoy posting problems: someone will be smarter than me when I am stumped, so I get to learn ;)

Comment by Noel — Friday, January 27, 2006 @ 5:07 pm

Actually, I don’t think it works correctly for some values. Put a 3 in it right now, and you get this PAST Tuesday (and not that Tuesday is 3, not 4…). I think this version works correctly:

set @desireddate = dateadd(dd, ((6 + @desireddayofweek – datepart(dw, getdate())) % 7) + 1, getdate())

Comment by Jeffrey Cross — Friday, January 27, 2006 @ 1:19 am

okay, so you can code a little. but can you understand the formal representation of godel’s slingshot – who built a slingshot after the fasion of others such as quine, davidson, and church?

not that I’m saying I can (right now). but one metaphysics term paper later…

Comment by ryan — Thursday, January 26, 2006 @ 11:13 pm