locked
Filter SQL query by a Date parameter RRS feed

  • Question

  • Hi,

     

    It's the first time that i user SQL Report builder, but I seeded to create a report for Dynamics CRM.

     

    I made a SQL query to join 3 entity filtering by date

     The condition is this one :

    Where(
     (mp_productprice.mp_fromdate<=(' + @datetest + ') AND mp_productprice.mp_todate>=(' + @datetest + '))
     OR
     (mp_productprice.mp_fromdate IS NULL OR mp_productprice.mp_todate IS NULL)
     )

     

    It's working if I put :

    declare @datetest nvarchar(max)

    set @datetest='GETDATE()'

     

    but not working for :

     

    declare @datetest nvarchar(max)

    set @datetest=@Date_appli

     

    Where @Date_appli is a parameter of the report.

    Some one know how to do?

     

    Thanks you very much.

     

    Friday, October 21, 2011 3:33 PM

Answers

  • Hi Boogie311,

     

    I got you, actually, you should take use of the escape character when you refer to a date time format string in exec(@string_variable) expression.

    Such as if you specify the @string_variable=’select * from tbl where datefrom>2011-10-24 00:00’

    You would encounter an error. In normal case, that should be @string_variable=’select * from tbl where datefrom>’2011-10-24 00:00’, so please modify you where clause like below:

     

    WHERE

     (

     (mp_productpricelevel1.mp_fromdate<=(''' + @datetest + ''') AND mp_productpricelevel1.mp_todate>=(''' + @datetest + '''))

     OR

     (mp_productpricelevel1.mp_fromdate IS NULL OR mp_productpricelevel1.mp_todate IS NULL)

     )

     

    Thanks,
    Bill Lu


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Boogie311 Monday, October 24, 2011 11:56 AM
    Monday, October 24, 2011 10:48 AM

All replies

  • Hi,

    According to my understanding, you don't need to declare variable in query window. Just write the below query and click on the Refresh fields it will ask you to enter the value for @Date_appli parameter so just enter the dummy value in standard date format. Finally just click on OK.

     

    Where(
     (mp_productprice.mp_fromdate<=(' + @Date_appli+ ') AND mp_productprice.mp_todate>=(' + @Date_appli+ '))
     OR
     (mp_productprice.mp_fromdate IS NULL OR mp_productprice.mp_todate IS NULL)
     )

    Let me know if it helps you or not.


    Cheers!! Sumit
    Friday, October 21, 2011 4:07 PM
  • Hi,

    Could you use the sql below?

    Where @datetest BETWEEN mp_productprice.mp_fromdate AND mp_productprice.mp_todate OR mp_productprice.mp_fromdate IS NULL OR mp_productprice.mp_todate IS NULL
    


    Remember to mark as an answer if this post has helped you.
    Friday, October 21, 2011 5:36 PM
  • Yes you're right  I had declare variable because in fact it was easier to change the value one time, in order to test.

     

    but  :  (mp_productprice.mp_fromdate<=(' + @Date_appli+ ') AND mp_productprice.mp_todate>=(' + @Date_appli+ '))

     

    doesn't work too. I had this error :

     

    Conversion failed when converting date and/or time from character string.


    Friday, October 21, 2011 5:49 PM
  • Yes I have try aslo with bettwen 

    I think the problem is a different format bettween mp_productprice.mp_fromdate (and the other one) and @Date_appli


    mp_productprice.mp_fromdate Is a date attribue in CRM

    @Date_appli is a Parameter (Date/time) when you launch a report there a calendar near the field.

    if i use GETDATE() instead of @Date_appli it's work.

     

     

    • Edited by Boogie311 Friday, October 21, 2011 6:05 PM
    Friday, October 21, 2011 6:02 PM
  • Did

    Where @datetest BETWEEN mp_productprice.mp_fromdate AND mp_productprice.mp_todate OR mp_productprice.mp_fromdate IS NULL OR mp_productprice.mp_todate IS NULL

    work?


    Remember to mark as an answer if this post has helped you.
    Friday, October 21, 2011 8:25 PM
  • Did

    Where @datetest BETWEEN mp_productprice.mp_fromdate AND mp_productprice.mp_todate OR mp_productprice.mp_fromdate IS NULL OR mp_productprice.mp_todate IS NULL

    work?


    Remember to mark as an answer if this post has helped you.


    If I write :

    declare @datetest nvarchar(max)

    set @datetest=@Date_appli

    Where @datetest BETWEEN mp_productprice.mp_fromdate AND mp_productprice.mp_todate OR mp_productprice.mp_fromdate IS NULL OR mp_productprice.mp_todate IS NULL

    => Must declare the scalar variable "@datetest"

    If I write :

    declare @datetest nvarchar(max)

    set @datetest=@Date_appli

    Where ('+@datetest+') BETWEEN mp_productprice.mp_fromdate AND mp_productprice.mp_todate OR mp_productprice.mp_fromdate IS NULL OR mp_productprice.mp_todate IS NULL

    => Incorrect syntaxe near '21'    (We are the 21/10/2011)

    If I write :

     

    Where ('+@Date_appli+') BETWEEN mp_productprice.mp_fromdate AND mp_productprice.mp_todate OR mp_productprice.mp_fromdate IS NULL OR mp_productprice.mp_todate IS NULL

    => Conversion failed when converting date and/or time from character string

     

     



    • Edited by Boogie311 Friday, October 21, 2011 9:43 PM
    Friday, October 21, 2011 9:42 PM
  • Then you have to try

    1.  When @Date_appli is a DateTime

     

    Where @Date_appli BETWEEN mp_productprice.mp_fromdate AND mp_productprice.mp_todate OR mp_productprice.mp_fromdate IS NULL OR mp_productprice.mp_todate IS NULL
    

     

    2. When @Date_appli is a string

     

    Where '@Date_appli' BETWEEN mp_productprice.mp_fromdate AND mp_productprice.mp_todate OR mp_productprice.mp_fromdate IS NULL OR mp_productprice.mp_todate IS NULL
    

    Though I don't see any reason why @Date_appli should be a string.

     


    Remember to mark as an answer if this post has helped you.
    • Edited by Igor Gelin Sunday, October 23, 2011 7:09 PM
    Sunday, October 23, 2011 7:08 PM
  • 1) => Must declare the scalar variable 'Date_appi'

    Even if in the paramters tab I have put Name of the parametrer : @Date_appli, Value of the parameter : [Date_appli]

    By default @Date_appli is a Datetime ?

     

    2) => Incorect syntax near '@Date_appli'

     

    Thanks a lot for helping me.

     

    Sunday, October 23, 2011 9:16 PM
  • Does I need to put in the query:

     

    declare @Date_appli as a datetime

     

    or

     

    declare @Date_appli nvarchar(max)

    Sunday, October 23, 2011 9:17 PM
  • Hi Boogie311,

     

    Thanks for your post.

     

    Let’s go back to your post at first.

     

    The GETDATE function’s return type is Datetime, which is coordinate to the type of column mp_fromdate and mp_todate, that’s why it works fine. As for @datetest, which is a nvarchar type value, that’s why you encounter the error message ‘Conversion failed when converting date and/or time from character string’. So you must assure your parameter datatype is coordinate to the datatype of the relevant table columns. Please refer to the WHERE Clause below:

    Where(

      (mp_productprice.mp_fromdate<=cast(@datetest as datetime) AND mp_productprice.mp_todate>=cast(@datetest as datetime))

      OR

      (mp_productprice.mp_fromdate IS NULL OR mp_productprice.mp_todate IS NULL)

      )

     

    Thanks,
    Bill Lu


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Monday, October 24, 2011 6:53 AM
  • I have kee :

     

    declare @datetest nvarchar(max)

    set @datetest=@Date_appli

    an put :

    Where(
     (mp_productpricelevel1.mp_fromdate<=cast(@datetest as datetime)  AND mp_productpricelevel1.mp_todate>=cast(@datetest as datetime) )
     OR
     (mp_productpricelevel1.mp_fromdate IS NULL OR mp_productpricelevel1.mp_todate IS NULL)
     )

     

     

    =>Must declare the scalare variable "@datetest"

    Monday, October 24, 2011 7:25 AM
  • the same if I type :

     

    (
     (mp_productpricelevel1.mp_fromdate<=cast(@Date_appli as datetime)  AND mp_productpricelevel1.mp_todate>=cast(@Date_appli as datetime) )
     OR
     (mp_productpricelevel1.mp_fromdate IS NULL OR mp_productpricelevel1.mp_todate IS NULL)
     )

     

    For parameters does I need to write like this :

     (' + @datetest + ') or (' + @Date_appli + ')

    thanks.


    • Edited by Boogie311 Monday, October 24, 2011 7:29 AM
    Monday, October 24, 2011 7:28 AM
  • Hi Boogie311,

     

    I suggest you to recreate the report in report builder to have a try, create the relevant dataset with T-SQL statement which doesn’t contain declare keyword, such as the T-SQL below:

    ……
    Where(

      (mp_productprice.mp_fromdate<=cast(@Date_appli as datetime) AND mp_productprice.mp_todate>=cast(@Date_appli as datetime))

      OR

      (mp_productprice.mp_fromdate IS NULL OR mp_productprice.mp_todate IS NULL)

      )

     

    Then click ok, the system should assist you create the relevant report parameter automatically, it is unnessary for you to create the relevant parameter manually.

     

    If it still dosen’t work, I would suggest you to post it in SSMS to test whether it works fine. If it works fine, I would suggest you to enclose your T-SQL statement to a stored procedure, and then refer to the stored procedure to retrieve the data to the dataset in your report.

     

    Thanks,
    Bill Lu


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Edited by Zilong Lu Monday, October 24, 2011 10:36 AM
    Monday, October 24, 2011 7:53 AM
  •  

     

     

    I suggest you to recreate the report in report builder to have a try, create the relevant dataset with T-SQL statement which doesn’t contain declare keyword, such as the T-SQL below:

     


    Ho can I do that ?

     

     

    The complete query is like that :

     

    declare @SQL nvarchar(max)
    declare @datetest nvarchar(max)

    set @datetest='GETDATE()'

     

    set @SQL = '
    SELECT
    mp_product0.mp_name as ''mp_name'',
    mp_product0.mp_productcode as ''mp_productcode'',
    mp_product0.mp_productid as ''mp_productid'',
    ...

    mp_productpricelevel1.mp_fromdate as ''mp_productpricelevel1_mp_fromdate'',
    mp_productpricelevel1.mp_todate as ''mp_productpricelevel1_mp_todate'',
    ...

    from (' + @CRM_FilteredMp_product + ') as mp_product0

     

    left outer join (' + @CRM_FilteredMp_productpricelevel + ') as mp_productpricelevel1 on (mp_product0.mp_productid = mp_productpricelevel1.mp_productid)

    WHERE

    (
    (mp_productpricelevel1.mp_fromdate<=(' + @datetest + ') AND mp_productpricelevel1.mp_todate>=(' + @datetest + '))
    OR
    (mp_productpricelevel1.mp_fromdate IS NULL OR mp_productpricelevel1.mp_todate IS NULL)
    )

    '


    exec(@SQL)

    Monday, October 24, 2011 9:32 AM
  • Hi Boogie311,

     

    I got you, actually, you should take use of the escape character when you refer to a date time format string in exec(@string_variable) expression.

    Such as if you specify the @string_variable=’select * from tbl where datefrom>2011-10-24 00:00’

    You would encounter an error. In normal case, that should be @string_variable=’select * from tbl where datefrom>’2011-10-24 00:00’, so please modify you where clause like below:

     

    WHERE

     (

     (mp_productpricelevel1.mp_fromdate<=(''' + @datetest + ''') AND mp_productpricelevel1.mp_todate>=(''' + @datetest + '''))

     OR

     (mp_productpricelevel1.mp_fromdate IS NULL OR mp_productpricelevel1.mp_todate IS NULL)

     )

     

    Thanks,
    Bill Lu


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Boogie311 Monday, October 24, 2011 11:56 AM
    Monday, October 24, 2011 10:48 AM
  • It's working !!!! YES

     

    Thanks you very much to all to helping me.

     

     

     

    Monday, October 24, 2011 12:00 PM