0
AND trunc(ITOH.CREATION_DATE) = NVL(:CREATION_DATE,ITOH.CREATION_DATE)

Here Iam Explicitly trying to pass date using 'CREATION_DATE' parameter
The CREATION_DATE will be coming in 'MM-dd-yyyy' format and the IOTH.CREATION_DATE is a timestamp.
Please help me.

Sam Francis
  • 1
  • 2
  • 4
  • 1
    If you're passing a date as a string (and if your client/application allows it, you should pass it as an actual date) then convert that string to a date explicitly with `to_date()` and the expected format mask. – Alex Poole Feb 10 '22 at 09:53
  • Im passing it with date format but a different arrangement and i can only do it one format only, i just want the "IOTH.CREATION_DATE" to understand my passing format – Sam Francis Feb 10 '22 at 09:57
  • 1
    What Alex is saying: If it's a date then you should pass it as a date. If this is not possible for some reason, then pass a string. But if you pass a string then treat it as a string. If you want the string to get converted to a date, then convert it to a date in SQL using `TO_DATE` with the explicit date format. – Thorsten Kettner Feb 10 '22 at 10:10
  • 1
    On a side note: It's a bad idea to call a **timestamp** column creation_**date**. This can easily lead to errors in queries. `trunc(ITOH.CREATION_DATE)` seems superfluous, but isn't. `NVL(:CREATION_DATE, ITOH.CREATION_DATE)` looks clean, but leads to not finding rows, because of the unexpected time part. – Thorsten Kettner Feb 10 '22 at 10:17
  • _"Im passing it with date format but a different arrangement"_ Then you are not passing it as a DATE (oracle data type), but rather as a character string that you, as a human, recognize as representing a date. But then, since you are using functions that take a DATE as an input, oracle has to do an implied call to TO_DATE, where it will u;se the session setting of NLS_DATE_FORMAT to interpret the string. And the format of the string does not match NLS_DATE_FORMAT. – EdStevens Feb 10 '22 at 14:41

1 Answers1

-1

The way you put it,

AND TRUNC(ITOH.CREATION_DATE) = NVL(TO_DATE(:CREATION_DATE, 'mm-dd-yyyy'),
                                    TRUNC(ITOH.CREATION_DATE)
                                   )

(don't forget TRUNC within NVL, otherwise you might get unexpected result)

Littlefoot
  • 131,892
  • 15
  • 35
  • 57