[sql] SQL DATEPART(dw,date) need monday = 1 and sunday = 7

I have a Query where I get the WeekDay of a date but by default:

• Sunday = 1

• Moday = 2

• etc.

The function is:

DATEPART(dw,ads.date) as weekday

I need the result so:

• Sunday = 7

• Monday = 1

• etc.

Is there any shortcut to do this? Or I will have to do a CASE statement?

Looks like the DATEFIRST settings is the only way, but it's not possible to make a SET statement in a scalar/table valued function. Therefore, it becomes very error-prone to the colleagues following your code. (become a trap to the others)

In fact, SQL server datepart function should be improved to accept this as parameter instead.

At the meantime, it looks like using the English Name of the week is the safest choice.

Another solution is the following:

ISNULL(NULLIF(DATEPART(dw,DateField)-1,0),7)

You can tell SQL Server to use Monday as the start of the week using DATEFIRST like this:

SET DATEFIRST 1

This is caused by the account the SQL Server service is run under. For example;

If the SQL Server Service is run under DOMAIN\MyUserAccount then this will need to be a login and set with the relevant Language.

If this account isn't set then SQL Server will default to the sa account and the Language that runs under.

I found that our sa account was set to English which had Monday as DW = 2. The DOMAIN\MyUserAccount Account was setup and changed to British English and DW for Monday was being returned as 1.

Hope this helps

I think this could work:

  select
    case when datepart(dw,[Date]) = 1 then 7 else DATEPART(DW,[Date])-1 end as WeekDay

This will do it.

SET DATEFIRST 1;

-- YOUR QUERY

Examples

-- Sunday is first day of week
set datefirst 7; 
select DATEPART(dw,getdate()) as weekday


-- Monday is first day of week
set datefirst 1;
select DATEPART(dw,getdate()) as weekday

I would suggest that you just write the case statement yourself using datename():

select (case datename(dw, aws.date)
             when 'Monday' then 1
             when 'Tuesday' then 2
             when 'Wednesday' then 3
             when 'Thursday' then 4
             when 'Friday' then 5
             when 'Saturday' then 6
             when 'Sunday' then 7
        end)

At least, this won't change if someone changes the parameter on the day of the week when weeks begin. On the other hand, it is susceptible to the language chosen for SQL Server.

You would need to set DATEFIRST. Take a look at this article. I believe this should help.

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-datefirst-transact-sql

I think

DATEPART(dw,ads.date - 1) as weekday 

would work.

Try this:

CREATE FUNCTION dbo.FnDAYSADDNOWK(
          @addDate AS DATE, 
          @numDays AS INT
       ) RETURNS DATETIME AS

    BEGIN
    WHILE @numDays > 0 BEGIN
        SET @addDate = DATEADD(day, 1, @addDate)    
        IF DATENAME(DW, @addDate) <> 'sunday' BEGIN
            SET @numDays = @numDays - 1 
        END
    END

    RETURN CAST(@addDate AS DATETIME)
END

You can use this formula regardless of DATEFIRST setting :

((DatePart(WEEKDAY, getdate()) + @@DATEFIRST + 6 - [first day that you need] ) % 7) + 1;

for monday = 1

((DatePart(WEEKDAY, getdate()) + @@DATEFIRST + 6 - 1 ) % 7) + 1;

and for sunday = 1

((DatePart(WEEKDAY, getdate()) + @@DATEFIRST + 6 - 7 ) % 7) + 1;

and for friday = 1

((DatePart(WEEKDAY, getdate()) + @@DATEFIRST + 6 - 5 ) % 7) + 1;