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

Try using TO_CHAR() and TO_DATE() together :

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

Answer 2

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 3

First off, your problem is not coming from a time value in the prompt value, but rather the time value in SomeDate. Getting rid of that (making the date equal to midnight) will resolve the issue.

Your best bet, if you have the option of modifying the universe, is to create another object. I'm assuming you have an object named SomeDate whose SQL is a.somedate. Create another object, let's call it SomeDateOnly with a definition of trunc(a.somedate)* **.

Since SomeDateOnly will always be a midnight value, you can use Equal To with your prompts, which will produce SQL like:

trunc(a.somedate) = @variable('Prompt1')

which, when rendered by WebI, will produce:

trunc(a.somedate) = '16-08-2016 00:00:00'

This will return all records with a.somedate between 8/16/2016 at 00:00:00 and 8/16/2016 23:59:59.

Of course, you can use BETWEEN to select a range of dates:

 trunc(a.somedate) between @variable('Start Date') and @variable('End Date')

Even if you don't have access to the universe, you can still use the above syntax by modifying WebI's generated SQL. (I'm assuming that's what you've been doing, anyway).

If the above works for you, then the following is irrelevant, but I wanted to address it anyway:

The reason for the "invalid number" error you were receiving is because of the way WebI formats dates for SQL. If you have this string in your query:

A.SomeDate = TRUNC(@variable('Prompt1'))

then WebI will replace the @variable(...) with a date string, and render it as the following before sending it to Oracle:

A.SomeDate = TRUNC('16-08-2016 00:00:00')

This, of course, makes no sense to the TRUNC() function as there's nothing to tell it that it's actually a date value.

You could to_date the prompt first, but you have to use the correct date format. WebI sets the nls_date_format for each session to a non-default format, so you would have to use:

A.SomeDate = TRUNC(to_date(@variable('Prompt1')),'dd-mm-yyyy hh24:mi:ss')

But again, this is irrelevant since you need to trunc somedate, not the prompt response values.

---

*Better still, rename SomeDate to SomeDateTime, and name the new object SomeDate

**This is pretty common - having multiple objects for the same source field. Sometimes you want the date/time value (for listing specific transactions), but sometimes you just need the date (for counting transactions by date). So having both available is very useful.