Post reply

Finding the Correct Weekday Regardless of DateFirst

  • July 29, 2012 at 10:49 pm

    #1518990

    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 SAT

    Everything 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

Viewing post 61 (of 60 total)

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