Post reply

Day of the week

  • October 23, 2006 at 4:45 am

    #174972

    Hi,

     

    Is there any SQL Function to retrieve the Day of the week, like Sunday, Monday, etc... ?

  • October 23, 2006 at 4:50 am

    #667314

    SELECT DATENAME(dw, GETDATE())

  • October 23, 2006 at 5:09 am

    #667315

    Thanks Ken !

  • October 23, 2006 at 5:22 am

    #667316

    The function datepart gives you 1-7 for Sun-Sat. But beware of the SET DATEFIRST setting of your database/session.

    When having to deal with specific days of a week (e.g. Saturday, Sunday) I tend to make myself blind to the SET DATEFIRST setting by first extracting their values for known dates, e.g.

    set @dowSat=datepart(dw,'2006-10-21')

    set @dowSun=datepart(dw,'2006-10-22')

    Then doing

    if datepart(dw,some-date) = @dowSun

    will be safe.

  • October 24, 2006 at 11:49 am

    #667658

    I once ran some tests and determined that this should work (it can probably be cleaned up):

    declare @sourcedate datetime, @newdatefirst tinyint

    select @sourcedate=getdate(), @newdatefirst = 7

    select dayoftheweek=((datepart(dw, @sourcedate) + @@DATEFIRST + (6-@newdatefirst) )%7)+1

    so if you want the values 1-7 for Sun-Sat (@@DATEFIRST of 7) you can simply(?) do:

    select dayoftheweek=((datepart(dw, @sourcedate) + @@DATEFIRST -1)%7)+1

    This should give you the answer independent of what the current DATEFIRST settings are.

    (This can be made into a UDF for clarity if you think it's worthwhile)

Viewing 5 posts - 1 through 4 (of 4 total)

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