I am using Business Objects, which runs on top of an Oracle SQL database. I do not have access to PL or any kind of SQL command line, and I do not have write access to the database. I can only run queries as single commands, requiring a defined set of columns to be output.
I am able to take data from user prompts, which appear in the SQL as:
A.SomeDate BETWEEN @variable('Start') AND @variable('End Date')
A.SomeDate BETWEEN TRUNC(@variable('Start')) AND TRUNC(@variable('End Date'))
If you want to match
SomeDate values between 00:00:00 on Start and 23:59:59 on End you can either adjust the end date to have that time instead of the default midnight, or use a range instead of
WHERE A.SomeDate >= @variable('Start') AND A.SomeDate < @variable('End Date') + 1
+ 1 uses Oracle date arithmetic to give you the day after the variable value, so if the user picked "01/01/2016 12:00:00 AM" for both the start and end dates they would evaluate as 2016-01-01 00:00:00 and 2016-01-02 00:00:00 respectively. You can use the
interval syntax if you prefer.
By using less-than for the upper limit you get all records where
SomeDate is greater than or equal to the start date 2016-01-01 00:00:00 and less than the adjusted end date 2016-01-02 00:00:00 - which is the same as saying up to 2016-01-01 23:59:59. (Or if you has a timestamp column which has sub-second precision, up to 23:59:59.999...).