Can anyone tell me how to get last day’s transactions in between date range in a Webi XI R2 report - Using SQL Server 2005?
Example: Between Start Date and End Date (between 01/01/2010 and 01/31/2010) does not bring any data from 01/31/2010.
Thanks for your hlep.
jkurikesu (BOB member since 2008-09-29)
For some of our dates we must also specify the time. I think a date like 1/31/2010 will default to 1/31/2010 00:00:00. You may need to figure a way to make it 1/31/2010 23:59:59.
richardcottave (BOB member since 2006-03-30)
Richardcottave is correct, your issue is most likely the time element, either being ommited or used incorrectly.
Unless things have changed (I Honestly haven’t rechecked this), the Developer controls and the viewer controls for data calendars are different and produce different SQL. (If you’re hand entering the date, then this is NOT your issue).
It sounds like this particular issue was never fixed, or perhaps is an alternate form of the bug.
My original post was from a few years ago on this issue which provides some details:
Try messing with the time and see what you get.
JPetlev (BOB member since 2006-11-01)
Does anyone have code samples or suggestions how to handle this? 
I would like to attach either 23:59:59 PM to the end date (Ex: 1/31/2010 23:59:59 PM) or take the date as it is in the database even if the user enter only the date without timestamp.
Thanks Again
jkurikesu (BOB member since 2008-09-29)
Before you try to ‘handle’ it, take a look at SQL 2005’s datatype list… I know 2008 has a new datatype called “Date”, which EXCLUDES any timestamp, unlike DateTime.
If that exists in 2005 and you have no need of the timestamp (such as the dates stamps are really only daily info anyway), then you might be best served by modifying your database data type… then there should be no issue.
If it’s not available in 2005 or you cannot modify the database, then honestly your best solution would be training your users on exactly what ‘between’ returns. (ie: add one day to the end if you wish to include it in the result). You could even note it in the prompt text if you have control over that.
The only other way to handle this ‘easily’ (which is how I did so in my previous job) is to not use the prompt window at all, and use custom prompt windows that you can control yourself (we used Ajax/JSP pages). Simply tell those pages to add 1 to the enddate before it is passed to Business Objects. Those pages then opened the report using OpenDoc protocols. The only downside is when you’re IN a report and hit refresh, you are back to square one. It wasn’t a perfect solution but it did allow us an extreme amount of flexability when coding the prompt pages. True Cascading prompts, hidden optional prompts (before it was available in BOBJ), and all sorts of other things.
The final way would be quite difficult to implement and would require you to ‘double’ up your date objects, so that instead of saying “Hiredate” between X and Y, you’d end up with “HireDateStart” = x and “HireDateEnd” = Y, but that gets kind of messy in the universe. (HireDateStart would be your “HireDate” in the database, HireDateEnd would simply be HireDateStart + 1). I say this is difficult not because of technical reasons, but more from the learning curve for your users and how it would impact other prompts that are not ‘betweens’. You’d always have to tell users to use the “Start” version.
Again I think it’s best handled by Database variable change, then user training if that’s not possible.
Good luck.
JPetlev (BOB member since 2006-11-01)
Thanks every one who replied and even looked into my issue. I resolved the issue!! I have some date fields with timestamp and some without. I only had the issue when I used the date with timestamp in the prompt, it was basically missing all the records for the last day.
How I resolved it was, creating another dimension in the Universe without the timestamp (basically stripping off the timestamp off the same database object) and used that object for the report prompt.
jkurikesu (BOB member since 2008-09-29)