« The Chuck Norris Law of the Excluded Middle | Main | Houston 1836 [Hearts] Hispanics »
January 26, 2006
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!
Post this page to your del.icio.us account
Posted by Noel at 05:32 PM
tagged as:
Trackback Pings
TrackBack URL for this entry:
http://chattablogs.com/mt/mt-tb.cgi/26074
Listed below are links to weblogs that reference Find the Next Arbitrary Day of the Week in T-SQL:
Comments
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...
Posted by: ryan at January 26, 2006 11:13 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())
Posted by: Jeffrey Cross at January 27, 2006 01:19 AM
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 ;)
Posted by: Noel at January 27, 2006 05:07 PM
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.
Posted by: Noel at January 27, 2006 05:10 PM