0

I can't save my view when I use this query:

SET DATEFIRST 6

SELECT       
    ISNULL ((SELECT SUM(ISNULL(Qte_achat * Prix_unit, 0) + 
                        ISNULL(qte_achat2 * prix_unit2, 0)) AS SUM_Achat
             FROM dbo.Achat
             WHERE (DATEPART(week, date_achat) = DATEPART(week, GETDATE())) 
               AND (YEAR(date_achat) = YEAR(GETDATE())) 
               AND (used_by_vent = 'false')), 0) AS SUM_Achat
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Nedjmo_O
  • 11
  • 3
  • Does this answer your question? [Get first day of week in SQL Server](https://stackoverflow.com/questions/7168874/get-first-day-of-week-in-sql-server) In other words: use those answers to calculate the start and end of the week (agnostic of `DATEFIRST`) and filter based on those dates. Bonus is a sarge-able query – Charlieface May 22 '21 at 23:33
  • thank you but not this – Nedjmo_O May 23 '21 at 10:36

2 Answers2

2

Avoid using query that depends on such settings. It could easily break your query.

Since your week is start on 'Saturday', use a base reference date that is Saturday like 1900-01-06 in calculation to determine the week

(DATEDIFF(day, '1900-01-06', date_achat) / 7 = (DATEDIFF(day, '1900-01-06', getdate()) / 7
Squirrel
  • 23,507
  • 4
  • 34
  • 32
1

No, Datefirst is a property of the session.

You can't set it in a view. Suppose you had a query that selected from two different views and you could set datefirst differently for each, which setting would the query honour?

You need to set it before using your view, or in a stored procedure.

Stu
  • 30,392
  • 6
  • 14
  • 33