What exactly does TO_DATE return

I'm coming from SQL Server world, and I'm struggling a little with dates conversion in Oracle. I have a table with a DATE column. I'm trying to return rows from a contracts table filtered by date.

I can select the following from the contracts table:

SELECT DISTINCT 
    TradeDate,
    TO_DATE('2023/07/24', 'YYYY/MM/DD')
FROM Contracts
ORDER BY TradeDate DESC;

which happily returns all rows from the Contracts table, although strangely the result-set is shown with the two dates formatted differently:

but if I try to add a where clause comparing TradeDate to the date I've created from TO_DATE:

SELECT DISTINCT 
    TradeDate,
    TO_DATE('2023/07/24', 'YYYY/MM/DD')
FROM Contracts
WHERE TradeDate = TO_DATE('2023/07/24', 'YYYY/MM/DD')
ORDER BY TradeDate DESC;

I get the error:

ORA-01861: literal does not match format string
ORA-02063: preceding line from
01861. 00000 - "literal does not match format string"
*Cause: Literals in the input must be the same length as literals in the format string (with the exception of leading whitespace). If the "FX" modifier has been toggled on, the literal must match exactly, with no extra whitespace.
*Action: Correct the format string to match the literal.

There's clearly nothing wrong with the usage of the TO_DATE function in the first SELECT statement and it's the same expression I use in the WHERE clause. However the error appears to be related to how TO_DATE is called. What is going on and why can't I compare the two dates together? Also, why does the result-set show the two dates in different formats?

Update: turns out that TradeDate is, in fact, a string. Joy.

TO_DATE transforms a sting into a DATE value. Are you really sure column TradeDate is a DATE value? I would assume, it is rather a VARCHAR2 data type (which would be a poor design)

Your query

SELECT 
   TradeDate,
   TO_DATE('2023/07/24', 'YYYY/MM/DD')
...

would return two DATE values, and it is not possible to display the same data type in different formats - unless you do any explicit formatting.

Check the data type of the column, you can use

SELECT DUMP(TradeDate, 1016) 
FROM ...

It should return something like

Typ=12 Len=... 

For a list of data types see https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Data-Types.html#GUID-7B72E154-677A-4342-A1EA-C74C1EA928E6

Perhaps the data type is TIMESTAMP rather than DATE and your user session NLS_DATE_FORMAT setting is different to NLS_TIMESTAMP_FORMAT setting. This also could explain the different outputs.

Update

As you found out, TradeDate is a string. Usually you should compare DATE values with DATE values, not strings. You can do

SELECT DISTINCT 
    TO_DATE(TradeDate, 'YYYY/MM/DD') as TradeDate,
    TO_DATE('2023/07/24', 'YYYY/MM/DD')
FROM Contracts
WHERE TO_DATE(TradeDate, 'YYYY/MM/DD') = TO_DATE('2023/07/24', 'YYYY/MM/DD')
ORDER BY TradeDate DESC;

or with DATE literals:

SELECT DISTINCT 
    TO_DATE(TradeDate, 'YYYY/MM/DD') as TradeDate,
    DATE '2023-07-24'
FROM Contracts
WHERE TO_DATE(TradeDate, 'YYYY/MM/DD') = DATE '2023-07-24'
ORDER BY TradeDate DESC;

However, the best solution would be to change the data type of column TradeDate to proper DATE.