Hello all.
I've been converting some local InfoMaker reports to SSRS since we upgraded to v11, and overall it's been quite smooth. Except one particular case. The parameters for start and end date for sales are of the DateTime data type, whereas almost all of our other dates are just Date type (this is in the Report Setup System). The problem is that InfoMaker seems to respect the time entered by the user, while SSRS appears to assume the end of the day for the sale_end_dt regardless of the time entered along with the date. (On closer inspection I saw that there is no "EOD" option for DateTime fields like there is for Date fields.)
I've run traces and Tessitura is passing the same end date (11/01/2011 12:00:00 AM) to both InfoMaker and SSRS, but the data in SSRS includes sales throughout that last day while InfoMaker cuts them off after 11:59:59 the previous night, which seems to be the right approach.
Additionally, I've tried entering different times on the same end date (11/01/2011 12:00:01 AM; 11/01/2011 11:59:59 AM; 11/01/2011 10:00:00 PM), and SSRS returns the same data for them all, while InfoMaker includes sales only up to the specified time.
So it seems like Tessitura is passing the data correctly; InfoMaker appears to pass it back to the SQL stored procedure correctly, but SSRS passes it to the proc differently (both versions of the report use the same SQL stored procedure).
I'm tempted to add fields specifically for times, and stop using the DateTime fields altogether. But I'm curious - does anyone out there know of some issue with this data type that could be causing this problem? Should I be wary of using it in other reports?
Thanks very much!
Nathan
I have experienced something similar so what I usually do is edit the procedure itself to look for dates before 12:00 AM. Hopefully that can help you...
12:00AM ----Select DATEADD(D,-1,DATEADD(D, DATEDIFF(D,0,GETDATE())+1,0))
11:59PM ----Select DATEADD(S,-1,DATEADD(D, DATEDIFF(D,0,GETDATE())+1,0))
So If I want anything before 12:00AM I would filter the date in the procedure iteself to those before 12:00AM. There might be other way but that is what seemed to work for me so far.