DateAdd( on parameter

Question

Hello I'm currently writing a report based on weekly sales, I've attained my current figure correctly and that works with my @FirstdayofWeek and @LastDayOfWeek parameters and im now trying to replicate it for my Previous week, as you know previous week is -7 days behind

when I run it with this in my where clause

  and FirstDayOfWeek = dateadd(day,-7,'2014/06/02')
  and LastDayOfWeek = dateadd(day,-7,'2014/06/08')

it works and i get this figure for pre quantity and its correct

BUT when I do this for my parameter

AND dateadd(day,-7,w.FirstDayOfWeek) in (
              SELECT Item
              FROM DataWarehouse.dbo.ufnSplit(@FirstDayOfWeek, ',')
              )
        AND dateadd(day,-7,w.LastDayOfWeek) in (
              SELECT Item
              FROM DataWarehouse.dbo.ufnSplit(@LastDayOfWeek, ',')
              )

I get the column headers nothing anywhere. any ideas?

Here is the code I am using to execute the stored proc:

exec WeeklySalesAndUSW @BD=N'798664',
@CGNo=N'47',
@SCGNo=N'01,02,03,04,05,06,07,08',
@ProductClass=N'1',
@‌ProductCode=N'1108',
@Region=N'772',
@FirstDayOfWeek = '2014/06/02',
@LastDayOfWeek = '2014/06/08'

Why isnt my parameter passing this through? why does it work if I hard code the date in but when i make it dynamic it gets nothing?

where do `@FirstDayOfWeek` and `@LastDayOfWeek` get populated? What is the declaration for `ufnSplit()`? — TZHX, Aug 25 '15 at 09:49
@TZHX the unf split allows the user to select multiple start and end dates, this is because the report is built as a matrix in visual studio so for each date selected therw will be the product data and then the weeks data seperated per week — James Chaggar, Aug 25 '15 at 09:54
here my execution @TZHZ exec WeeklySalesAndUSW @BD=N'798664',@CGNo=N'47',@SCGNo=N'01,02,03,04,05,06,07,08',@ProductClass=N'1',@ProductCode=N'1108',@Region=N'772',@FirstDayOfWeek = '2014/06/02',@LastDayOfWeek = '2014/06/08' — James Chaggar, Aug 25 '15 at 09:55
@TZHX if you look at the first screenshot and check the dates against my execution plan ? — James Chaggar, Aug 25 '15 at 09:59
what is your sql server version ? what is the compatibility ?? — Arunprasanth K V, Aug 25 '15 at 13:05

score 0 · Answer 1 · answered Aug 25 '15 at 13:03

It's probably not working because you reversed the equation.

In the code that works, you are doing this:

and FirstDayOfWeek = dateadd(day,-7,'2014/06/02')
and LastDayOfWeek = dateadd(day,-7,'2014/06/08')

Notice that you are subtracting 7 days from the hard-coded "parameters", and not from the columns in the table.

Now that you are trying to use dynamic parameter values, you are doing the opposite:

AND dateadd(day,-7,w.FirstDayOfWeek) in (
              SELECT Item
              FROM DataWarehouse.dbo.ufnSplit(@FirstDayOfWeek, ',')
              )
        AND dateadd(day,-7,w.LastDayOfWeek) in (
              SELECT Item
              FROM DataWarehouse.dbo.ufnSplit(@LastDayOfWeek, ',')
              )

You are subtracting 7 days from the table column, and not the parameter values.

You can correct this by simply removing the minus (-) sign before both of the 7's. This is because adding 7 to the left side of the equation is mathematically the same as subtracting 7 from the right side.

DateAdd( on parameter

1 Answers1