Passing a user prompt as a date (or even 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 SQL command line, and I do not have write access to the database. I can only run queries as single commands, requiring a specific set of columns to be output.

I can take data from user queries that appear in SQL as:

@variable('Prompt1') 

For example, I can say:

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

It is quite simple. He works; asks the user to enter some dates; and then returns all matches that are between them (inclusive).

However, the problem is that users will use the Infoview Business Objects system to run queries, and the invitation system is a date picker, which by default includes the time part of the date ("01/01/2016 12:00:00 AM") .

If the user does not delete the time part, this may lead to a skipping of the record if the value of SomeDate is outside the selected time. For example, if I want to take all the records TODAY, then I technically want everything between 00:00:00 (midnight) and 23:59:59.

What I really would like to do is use TRUNC around the query variable, as shown below:

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

... however, this causes a compilation error: "inconsistent data types: expected DATE received NUMBER". I do not know why Oracle will treat the prompt as a numeric data type before compiling it.

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

So I'm trying to find a way around this. One thing I had in mind is if I can take the invitation variable and convert it explicitly to a date using TO_DATE

Edit: I was told that TRUNC will not have any effect since "12:00:00 AM" is already midnight. Therefore, I think I misunderstood TRUNC. It looks like it truncates it until midnight: while I thought it was just deleting the time part of the date at all, which means matches will be returned anytime between 00:00:00 and 23:59:59.

I really want: if SomeDate has a temporary part, for example, 11:03, then how can I make sure this is turned on when the prompt at the end of the date indicates only the day?

Answer 1

If you want to map SomeDate values ​​between 00:00:00 at Start and 23:59:59 at the end, you can either configure the end date to have this time instead of the default midnight, or use a range instead of of between :

 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 selects "01/01/2016 12:00:00 AM" for the start and end dates, which they will evaluate as 2016-01-01 00 : 00: 00 and 2016-01-02 00:00:00 respectively. You can use interval syntax if you want.

Using less than the upper limit, you get all the records where SomeDate 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 - this is the same as before 2016-01-01 23:59:59. (Or, if you have a timestamp column that has accuracy over the second period, up to 23: 59: 59.999 ...).

If the parser assumes that the variable will be a string, but in fact it is the date causing the "inconsistent data type" error, then you can send it to the 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 specified, 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 

... make sure you have the correct format; from your example, this 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') 

Answer 3

Firstly, your problem is not related to the time value in the prompt value, but rather the time value in SomeDate . Getting rid of this (to make the date equal to midnight) will solve the problem.

Best of all, if you have the ability to change the universe, you need to create another object. I assume that you have an object called SomeDate whose SQL is a.somedate . Create another object, call it SomeDateOnly with the definition trunc(a.somedate) * **.

Since SomeDateOnly will always be a value at midnight, you can use Equal To with your prompts that will generate 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 using 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 date range:

  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 the generated WebI SQL. (I guess you did anyway).

If the above works for you, then the following does not matter, but I would still like to refer to it:

The cause of the "invalid number" error you received is related to how the WebI format formats dates for SQL. If you have this line in your query:

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

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

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

This, of course, does not make sense for the TRUNC () function, since there is nothing to say that it is actually a date value.

At first you can to_date execute the query, but you must use the correct date format. WebI sets nls_date_format for each session to a format other than the standard, so you will need to use:

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

But then again, that doesn't matter, since you need a trunc SomeDate , not a prompt response value.

---

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

** This is quite common - having multiple objects for the same source field. Sometimes you need a date / time value (to list specific transactions), but sometimes you just need a date (to count transactions by date). Therefore, having both available is very helpful.