>=DateAdd("ww",-9,Date()-Weekday(Date(),0)+1))
I have the above function in some SQL coding that I have inherited and am struggling to work out what it is actually calculating can anyone help?
Asked
Active
Viewed 166 times
-1
-
It looks more like an MS Excel or Access function. – gofr1 Jun 01 '16 at 10:04
-
I thought this was DAX, but it's not, since DAX has no `DATEADD` function. But T-SQL has no `WEEKDAY` function, so this is probably just plain VBA. Add the context for where you found this code. – Jeroen Mostert Jun 01 '16 at 10:06
-
Could these be user defined SQL functions? – Alex Jun 01 '16 at 10:32
-
1@Alex: no, because invoking such functions requires specifying the schema (`dbo.Date`). This is not T-SQL. – Jeroen Mostert Jun 01 '16 at 10:45
-
@Jeroen Mostert, how could I have forgotten this. – Alex Jun 01 '16 at 10:50
1 Answers
1
Consider this SQL with the statement deconstructed into parts.
It was tested in sql-server 12, and uses the GETDATE() instead of the Date() function.
select
GETDATE() as today,
@@DATEFIRST as first_day_of_the_week_number,
DATEPART(WEEKDAY,GETDATE()) as current_day_number_of_the_week,
GETDATE()-DATEPART(WEEKDAY,GETDATE()) as previous_saturday,
GETDATE()-DATEPART(WEEKDAY,GETDATE())+1 as previous_sunday,
DATEADD("ww",-9,GETDATE()-DATEPART(WEEKDAY,GETDATE())+1) as previous_sunday_9_weeks_back;
So it would give the sunday of 9 weeks back.
Note that it assumes that @@DATEFIRST equals 7.
If @@DATEFIRST equals 1 then it would return the monday of 9 weeks back.
LukStorms
- 28,916
- 5
- 31
- 45
-
2That actually depends entirely on the value of [`@@DATEFIRST`](https://msdn.microsoft.com/library/ms187766). (But the `WeekDay` function is likewise ambiguous with a second argument of `0`.) – Jeroen Mostert Jun 01 '16 at 10:29
-