Accepting 2 Date Parameters at runtime

Toad for Oracle & DevOps Toolkit
1

I have an Issue which no sooner did i give the two Date parameters at run time it is Terminating and reverting the Output as no Rows Returned… Can Oracle Accepts two Date Parameters.

The Code i’ve written is Executed when it was hardcoded but when i give the parameters; i’m Facing an Issue with it…

Here is the attached Snippet–toad check.png

However When i Removed one date parameter; it is reverting.

Can Someone Give me a Brief Description…

Thank you

2

Afternoon Praveen,
On 08/11/14 03:35, Praveen Kaligotla wrote:

*Accepting 2 Date Parameters at runtime
...
A couple of questions for you:

  1. What is the data type of the column DATE_OF_INVOICE in the table o8914462? Is it DATE or TIMESTAMP or something else? Something else is bad, very very bad!
  2. What is the current NLS_DATE_FORMAT parameter?
  3. Why are you comparing strings with DATEs? Assuming of course that the table does have a DATE or TIMESTAMP data type for the DATE_OF_INVOICE column.
    Oracle will accept as many literals as you want to supply. Up to a limit of 1,000 if I remember correctly (at least for an "IN" clause anyway!)
    If you are running the code in Toad, and it is reverting - and I'm not sure what you mean by that, sorry - you can go to Database->Spool SQL->Spool to screen to get Toad to display the SQL that it sends to the database for your query. The output from that might be interesting.
    If you are storing the dates as VARCHAR2, and in the format shown in your post (DD-MON-YYYY) then there's practically no way you can check for dates that are in the range you define - 01-JAN-2013 to 02-JAN-2013 because other dates in the table may also be selected, plus others that you might want could (!) be ignored.
    In addition, unless the application AND the database do lots of validation on the supplied "dates", there's absolutely nothing to stop 'Hello World' or '2013/01/02' etc being accepted as valid VARCHAR2 values, and this will also cause problems when Oracle tries to convert it to a date at some point.
    For example, '01-DEC-2014' is considered less than '02-JAN-2013' because '01' is definitely less than '02' at the start of the string. This is why you should never ever store DATEs as anything but DATE data type (or TIMESTAMP depending on how accurate the DATE/TIME part has to be of course).
    If the dates are really DATEs, the query you supplied is actually (implicitly) converting them to VARCHAR2 to compare them with your string values.
    Whenever you compare a column's data with a literal or value that is not the same data type as the column, Oracle converts the column's data into the data type of the literal to make the comparison.
    This implicit conversion to VARCHAR2, in your case, will prevent the ability to use any indexes on the DATE_OF_INVOICE column causing a full table scan - which might not be a bad thing though - and all the caveats above about comparing dates as strings apply.
    Try changing your dates from '02-JAN-2013' to TO_DATE('02-JAN-2013', 'DD-MON-YYYY') and from '01-JAN-2013' to TO_DATE('01-JAN-2013', 'DD-MON-YYYY') - which will allow Oracle to compare DATEs with Dates and guarantee that any date in the desired range can be selected.
    Some more questions:
  4. How many rows are you expecting to see from the query?
  5. How many do you get in reality?
  6. What do you mean by "it is reverting"? I realise that English may not be your main language, and I apologise for asking a simple question. Sorry.
    The rules for date handling in Oracle are simple:
  • When storing dates, always use a DATE data type, or perhaps a TIMESTAMP of some kind.
3

Fine. Working Now Appended ADDTIME() Function whichs adds as my Concern for Getting the Answer…