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
Thanks Renville. I think I didn't explain it quite right, the report allows users to enter a time along with the date, so they can capture sales between noon and 4pm for example... except that the end date was always changing the time to 11:59 PM.
I eventually figured it out. In Report Setup, this field was originally a Date type field with the EOD option checked; it was later changed to DateTime type which doesn't have that option, but "behind the scenes" it was apparently holding on to that property. I changed the field back to Date, un-checked the EOD box, changed the field type to DateTime again... and it works as intended.
It's interesting that InfoMaker didn't recognize the phantom EOD property, but SSRS did.
just a note to say that this thread was just EXTREMELY helpful!! how silly that it secretly hangs on to end of day...