SQL DATEPART dw date need monday 1 and sunday 7

Question

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

User · Answer

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.

User · Answer

Try this   CREATE FUNCTION dbo FnDAYSADDNOWK             addDate AS DATE              numDays AS INT          RETURNS DATETIME AS      BEGIN     WHILE  numDays  gt  0 BEGIN         SET  addDate   DATEADD day  1   addDate              IF DATENAME DW   addDate   lt  gt   sunday  BEGIN             SET  numDays    numDays - 1          END     END      RETURN CAST  addDate AS DATETIME  END

User · Answer

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

User · Answer

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

User · Answer

You can use a formula like    weekday   5    7   1   If you decide to use this  it would be worth running through some examples to convince yourself that it actually does what you want   addition  for not to be affected by the DATEFIRST variable  it could be set to any value between 1 and 7  the real formula is     weekday      DATEFIRST   5    7   1

User · Answer

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

User · Answer

Another solution is the following   ISNULL NULLIF DATEPART dw DateField -1 0  7

User · Answer

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

User · Answer

I think this could work     select     case when datepart dw  Date     1 then 7 else DATEPART DW  Date  -1 end as WeekDay

User · Answer

I think   DATEPART dw ads date - 1  as weekday    would work

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server