Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Finding the Correct Weekday Regardless of DateFirst Rate Topic Display Mode Topic Options
Author
 Message
 Posted Sunday, July 29, 2012 10:49 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Friday, November 4, 2016 3:03 AM Points: 156, Visits: 604
 rramsey (7/29/2012)One thing I noticed is this: I expected saturday to be returned as day 7 when sunday was day 1 however; the formula, Declare @ThisDay int = (DATEPART(weekday,@ThisDate) + @@DATEFIRST) % 7, always returns 0 for saturday. this query will show the results for each day of the week from 7/22/2012 to 7/28/2012.Select @@DATEFIRST as [DateFirst], ((DATEPART(weekday,'7/22/2012') + @@DATEFIRST) % 7) as SUN, ((DATEPART(weekday,'7/23/2012') + @@DATEFIRST) % 7) as MON , ((DATEPART(weekday,'7/24/2012') + @@DATEFIRST) % 7) as TUE, ((DATEPART(weekday,'7/25/2012') + @@DATEFIRST) % 7) as WED, ((DATEPART(weekday,'7/26/2012') + @@DATEFIRST) % 7) as THR, ((DATEPART(weekday,'7/27/2012') + @@DATEFIRST) % 7) as FRI, ((DATEPART(weekday,'7/28/2012') + @@DATEFIRST) % 7) as SATEverything works well as long as you expect saturday to be zero.Yes Andy Tyan has explained how to handle it. Read two comments before this comment..he has explained in it. --Divya
Post #1337059

 Permissions