-1

Here is my query:

SELECT  ID AS 'securityid'
      , date
      , DATEADD(DW, 1, adate) AS 'lagged_date_v2'
      , DATEADD(DAY, 1, adate) AS 'lagged_date_v1'
      , aclose AS 'previous_close'
FROM    mytable
WHERE   adate BETWEEN '20170101 09:00' AND '20170630 18:00' AND
        ID = 100056;

Here are the output from the code above:

Outputs image

I compared the results to DATEADDby day and by weekday and it returns the same results.

Column adate is just typical date, we can tell Jan 28, 2017 is Saturday. However, on this row, both lagged_date_v1 and lagged_date_v2 return Jan 28. If I use DATEADD by weekday correctly, I should see Jan 30 instead Jan 28, right?

My sql server version is 2008.

Cameron
  • 2,574
  • 22
  • 37
Aaron
  • 7
  • 1
  • 4
  • 5
    "dayofyear, day, and weekday return the same value." https://learn.microsoft.com/en-us/sql/t-sql/functions/dateadd-transact-sql; see https://stackoverflow.com/questions/5471524/add-business-days-to-date-in-sql-without-loops – Paul Abbott Jun 19 '17 at 15:56

1 Answers1

3

If I use DATEADD by weekday correctly, I should see Jan 30 instead Jan 28, right?

Wrong. In SQL Server, weekday doesn't mean days that aren't weekend days. It means which day of the week (1-7) is the day in question based on the current datefirst setting.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • So wouldn't that be entirely redundant with `day`? – StackOverthrow Sep 24 '18 at 20:42
  • 1
    @TKK No, `day` is "day of the month" (eg 1st, 15th, 30th). `weekday` is "day of the week" (eg: Sunday, Wednesday, Friday). Although for purposes of the first parameter of `DATEADD`, yes, they are the same. – Tab Alleman Sep 25 '18 at 13:55