Passing a user prompt as a date (or even a string) in Oracle SQL

Question

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:

@variable('Prompt1')

For example I can say:

SELECT
    A.SomeDate
FROM
    A
WHERE
    A.SomeDate BETWEEN @variable('Start') AND @variable('End Date')

This is easy enough. It runs; requests the user to input some dates; and then returns all matches which are between them (inclusive).

The problem is, however, the users will be using Business Objects' "Infoview" system to run the queries, and the prompt system presents a date picker - which by default includes the time portion of the date ("01/01/2016 12:00:00 AM").

If the user does not delete the time portion, it can cause records to be missed, if the SomeDate value falls outside the selected time. For example, if I want to take all records of TODAY, then I technically want everything between 00:00:00 (midnight) and 23:59:59.

What I would really like to be able to do is use TRUNC around the query variable, as follows:

WHERE
    A.SomeDate BETWEEN TRUNC(@variable('Start')) AND TRUNC(@variable('End Date'))

... however this causes a compilation error: "inconsistent datatypes: expected DATE got NUMBER". I don't know why Oracle would treat a prompt as a number datatype before it has compiled.

Does anyone know how I can take the @variable value and convert it into something that I will be able to truncate to a date value?

I'm therefore trying to figure out a way round this. One thing I had in mind was if I could possibly take the prompt variable and convert it explicitly into a date, using TO_DATE

Edit: it has been pointed out to me that TRUNC will have no effect, as the "12:00:00 AM" is already midnight. Therefore I think I have misunderstood TRUNC. It appears that it truncates it to midnight: whereas I thought it simply removed the time portion of the date altogether, meaning that matches would be returned at any time between 00:00:00 and 23:59:59.

What I really want is: if SomeDate has a time portion of, for example, 11:03 then how do I ensure that this will be included when an End Date prompt only specifies the day?

Answer 1

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 between:

WHERE
    A.SomeDate >= @variable('Start')
AND
    A.SomeDate < @variable('End Date') + 1

The + 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...).

If the parser assumes the variable will be a string but it is actually a date - causing an 'inconsistent datatypes' error - then you could cast it to a date to satisfy the parser:

WHERE
    A.SomeDate >= CAST(@variable('Start') AS DATE)
AND
    A.SomeDate < CAST(@variable('End Date') AS DATE) + 1

or if it is actually passed as a string in the format you showed you can explicitly convert it:

WHERE
    A.SomeDate >= TO_DATE(@variable('Start'), 'DD/MM/YYYY HH:MI:SS AM')
AND
    A.SomeDate < TO_DATE(@variable('End Date'), 'DD/MM/YYYY HH:MI:SS AM') + 1

... making sure you have the correct format; from your example it could be DD/MM/YYYY or MM/DD/YYYY.

Answer 2

Try using TO_CHAR() and TO_DATE() together :

WHERE
A.SomeDate > TO_DATE(TO_CHAR(@variable('Prompt1'),'ddmmyyyy'),'ddmmyyyy')