sqloracle-database

TO_CHAR and TO_DATE use and the right context to use them

If I have a query like:

select *
from CAT_ACCT_AUDIT_TRAIL cataccount0_
where cataccount0_.CAAT_EXECUTED_DATE >=TO_DATE('26-AUG-2016', 'DD-MM-YYYY') AND
       to_Date(TO_CHAR(cataccount0_.CAAT_EXECUTED_DATE , 'dd -mon-yyyy'), 'DD-MM-YYYY')<=TO_DATE('31-AUG-2016', 'DD-MM-YYYY')

Here why do we require the to_char or to_date functions? What is the right context to use them?

If I do either of these:

select TO_DATE('26-AUG-2016', 'DD-MM-YYYY') from dual;
select TO_DATE('01-12-2016', 'DD-MM-YYYY') from dual;

I get the output in NLS variable format as I set in the session, irrespective of date input in format conversion; I get the same result for both. Wy is this so?

What is the correct way to solve this query? I mean when i need to fetch the values in date range.

Solution

  • You use to_date() to convert a string like '01-12-2016' to the date datatype. You use to_char() to convert a date to a string. In both cases you specify the format of the string - if you don't then your session NLS settings are used, which is not good practice for anything except ad hoc queries as someone else running your code later may get a different output or an error.

    A general rule - which your code is following - is to compare data of one type with values/constants of the same type. As your column is a date, you're supplying the filter values as dates - by converting strings to the date datatype. If you didn't do that then implicit conversion would happen, but you should not rely on that either as it can also lead to NLS issues, and depending on the type it can prevent indexes being used. Read more about data conversion in the documentation.

    Oracle tries to be flexible when interpreting the string when you do to_date(). When you do TO_DATE('26-AUG-2016', 'DD-MM-YYYY') you are supplying the month as a string (in a specific language, which is another topic), but telling the function to expect a number. Oracle 'helpfully' interprets that anyway, so it usually works. But whatever format you use for to_date(), you aren't specifying the display format, so your client is deciding how to display the converted date as a string for you - usually using your NLS settings, again.

    Doing this:

    to_Date(TO_CHAR(cataccount0_.CAAT_EXECUTED_DATE , ‘dd -mon-yyyy’), ‘DD-MM-YYYY’)

    is usually pointless, but even so should be using consistent format models. One reason this is sometimes done is if the source date (caat_executed_date here) has its time set to something other than midnight, and you want to discard the time. But there are better ways to do that - specifically the trunc() function, which by default sets the time to midnight.

    When you have constant values, like TO_DATE('31-AUG-2016', 'DD-MM-YYYY'), you can also use ANSI date literals, in the form of DATE '2016-08-31'.