Filter SQL query by a Date parameter
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.
October 21st, 2011 11:44am

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
Free Windows Admin Tool Kit Click here and download it now
October 21st, 2011 12:18pm

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.
October 21st, 2011 1:47pm

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.
Free Windows Admin Tool Kit Click here and download it now
October 21st, 2011 2:00pm

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.
October 21st, 2011 2:13pm

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
Free Windows Admin Tool Kit Click here and download it now
October 21st, 2011 5:53pm

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.
October 23rd, 2011 3:10pm

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.
Free Windows Admin Tool Kit Click here and download it now
October 23rd, 2011 5:18pm

Does I need to put in the query: declare @Date_appli as a datetime or declare @Date_appli nvarchar(max)
October 23rd, 2011 5:20pm

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.
Free Windows Admin Tool Kit Click here and download it now
October 24th, 2011 2:56am

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"
October 24th, 2011 3:27am

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.
Free Windows Admin Tool Kit Click here and download it now
October 24th, 2011 3:31am

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.
October 24th, 2011 3:56am

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)
Free Windows Admin Tool Kit Click here and download it now
October 24th, 2011 5:34am

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.
October 24th, 2011 6:51am

It's working !!!! YES Thanks you very much to all to helping me.
Free Windows Admin Tool Kit Click here and download it now
October 24th, 2011 8:09am

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.
October 24th, 2011 1:48pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics