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