What exactly does TO_DATE return

Question

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:

TradeDate	TO_DATE('2023/07/24', 'YYYY/MM/DD')
2024/01/23	24-JUL-23
2023/12/21	24-JUL-23
2023/12/20	24-JUL-23
2023/12/19	24-JUL-23
2023/12/18	24-JUL-23
2023/12/15	24-JUL-23

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.

Answer 1

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.

Answer 2

TO_DATE function in oracle will take three arguments like this

TO_DATE (string, format, nls_language)

Second and third argument is optional

First argument is a string value which we need to converted to a DATE value. It can be a value of any data type CHAR, VARCHAR2, NCHAR, or NVARCHAR2.

Second argument is format argument is optional. If you omit the format, the string must be in the standard oracle date format which is DD-MON-YY e.g., 27-DEC-23.

Third argument is an expression that specifies the language for day and month names in the string. This nls_language argument has the form "NLS_DATE_LANGUAGE = language" and this ls_language argument is optional. If you omit it, the TO_DATE() function will use the default language for your session.

In most cases, only first 2 arguments will use for to_date .The TO_DATE() function returns a DATE value which is corresponding to the input string.

so in your case

WHERE   TradeDate = TO_DATE('2023/07/24', 'YYYY/MM/DD')

i am assuming "TradeDate" column is having "VARCHAR" or any other datatype other than date so u cant compare a string to date format. so u need to convert the TradeDate column into DATE format for comparing.

if TradeDate having values like this "2024/01/23" then u need to use TO_DATE(TradeDate,'YYYY/MM/DD'). [ i.e. Use the format which is there in the column of TradeDate ]

so try like this

SELECT  DISTINCT 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 TO_DATE(TradeDate,'YYYY/MM/DD') DESC;

if u want to use order by DATE then u also need to convert that to date format otherwise it will order by string desc

Answer 3

To just answer the question from title "What exactly does TO_DATE return" (now when it is clear that datatype of TradeDate is VarChar)
TO_DATE() function converts a string into Oracle DATE datatype meaning that...
TO_DATE() function returns 7 bytes representing Century, Year, Month, Day, Hours, Minutes and Seconds.

Answer 4

Looking at your output of the first query:

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

It seems like

• Your NLS_DATE_FORMAT is DD-MON-YY (See TradeDate in the result --> 24-JUL-23)
• Column TradeDate is VARCHAR column with dates as a string in the YYYY/MM/DD format.

Now, let's look into your second query where you are trying to compare the TradeDate column data with some date which is TO_DATE('2023/07/24', 'YYYY/MM/DD')

WHERE TradeDate = TO_DATE('2023/07/24', 'YYYY/MM/DD')

Oracle will try to compare this VARCHAR column to DATE literal after converting the VARCHAR column to DATE data type.

For converting the string to date, oracle will use the format specified in the NLS parameter NLS_DATE_FORMAT which is DD-MON-YY for your session.

Means Oracle will try to do this conversion as follows:

TO_DATE(TradeDate,'DD-MON-YY')
--Taking one of the examples from data of TradeDate column
TO_DATE('2024/01/23','DD-MON-YY')

Which is obviously not convertible so Oracle is throwing an error.

You need to convert your TradeDate to date before comparing it with date as follows:

WHERE TO_DATE(TradeDate,'YYYY/MM/DD') = TO_DATE('2023/07/24', 'YYYY/MM/DD')

OR you can simply do string comparison as follows:

WHERE TradeDate = '2023/07/24'