Date parameter problems in 7.2
25 May, 2016
I upgraded a customer from version 6.3 to 7.2. One report is causing particular difficulties. It is based on a Stored Procedure view, and the stored procedure takes a single parameter of type datetime. The underlying database is SQL Server 2005, and therefore doesn't support a date data type.
The report is set to default to a pre-determined date for the filter - the start of the current month. This worked fine in 6.3 but now in 7.2 instead of passing a single datetime value, it would appear that using an equals filter on a datetime actually creates SQL like this :
"BETWEEN '2016-05-01 00:00:00.000' AND '2016-05-01 23:59:59.997'"
which doesn't do the stored procedure much good when it is expecting just a single date.
I've tried rewriting the report using direct SQL to the stored procedure, but then I don't get the option to use pre-determined dates at all, so I can't think of a way to rescue the report without re-developing the stored procedure.
Is there any magic setting I can use to get a single value out of a pre-determined date in 7.2?
The report is set to default to a pre-determined date for the filter - the start of the current month. This worked fine in 6.3 but now in 7.2 instead of passing a single datetime value, it would appear that using an equals filter on a datetime actually creates SQL like this :
"BETWEEN '2016-05-01 00:00:00.000' AND '2016-05-01 23:59:59.997'"
which doesn't do the stored procedure much good when it is expecting just a single date.
I've tried rewriting the report using direct SQL to the stored procedure, but then I don't get the option to use pre-determined dates at all, so I can't think of a way to rescue the report without re-developing the stored procedure.
Is there any magic setting I can use to get a single value out of a pre-determined date in 7.2?
Good Afternoon Andy,
Hope you are well,
Thank you for sending your question over, I have been looking in to this for you and while there is no magic setting in Yellowfin to get a single value out, I have found some information on line about using a different option to get the pre-defined date field out of SQL 2005
I have included a link to the Microsoft that talks about using using smalldatetime in queries.
https://msdn.microsoft.com/en-us/library/ms187819%28v=sql.90%29.aspx
Please read through and let me know if this assist you in getting the information you need.
If you do have any further questions please feel free to contact us.
Thanks
Adam
Hope you are well,
Thank you for sending your question over, I have been looking in to this for you and while there is no magic setting in Yellowfin to get a single value out, I have found some information on line about using a different option to get the pre-defined date field out of SQL 2005
I have included a link to the Microsoft that talks about using using smalldatetime in queries.
https://msdn.microsoft.com/en-us/library/ms187819%28v=sql.90%29.aspx
Please read through and let me know if this assist you in getting the information you need.
If you do have any further questions please feel free to contact us.
Thanks
Adam