excel - ORA-01861: literal does not match format string despite of using to_date function

Question

Facing this error on EXCEL VBA Module. On Oracle 11g interface it runs well.

select 
        "schema_name", "table_name", "bundle_name", "text_file", "sequence_no", 
                    lastupdateapplied, "next_sat_date","prev_sat_date", TO_DATE(SYSDATE,'DD-MM-YYYY'),
        CASE 
                WHEN ("schema_name"='EDM_V1' OR "schema_name"='FF_V3' OR "schema_name"='SYM_V1') and 
                            ("next_sat_date"-lastupdateapplied<=0 OR 
                            lastupdateapplied-"prev_sat_date">=0) 
                then 'EVERYTHING LOOKS OK HERE.'
                WHEN  ("schema_name"='FP_V2' OR "schema_name"='FE_V4' OR "schema_name"='REF_V2') and 
                            (lastupdateapplied - TO_DATE(SYSDATE,'DD-MM-YYYY') =0)
                then 'EVERYTHING LOOKS OK HERE.'
                Else 'PLEASE CHECK THE SCHEDULER FOR THIS FEED.' END as    "VERIFICATION_NOTE"
from
        ( 
            select 
                    UPPER(ds."schema_name") as "schema_name", UPPER(ds."table_name") as "table_name", ds."bundle" as "bundle_name", ds."text_file" as "text_file", 
                    ds."sequence" as "sequence_no", TO_DATE(s2.end_time,'DD-MM-YYYY') as lastupdateapplied, TO_DATE(next_day(SYSDATE,'SATURDAY'),'DD-MM-YYYY') as "next_sat_date",  
                    TO_DATE(next_day(SYSDATE,'SATURDAY')-(INTERVAL '7' DAY + INTERVAL '1' SECOND),'DD-MM-YYYY') AS "prev_sat_date"
            from FDS_FDS_DATA_SEQUENCES ds 
            Join 
            (   select "table_name", "bundle", "text_file", max("end_time") as end_time 
                from FDS_FDS_FILE_HISTORY 
                where "file_type" = 'full' 
                group by "table_name", "bundle", "text_file") s 
             on s."bundle" = ds."bundle" and s."table_name" = ds."table_name" and s."text_file" = ds."text_file"
            Join 
            (   select "table_name", "bundle", "text_file", max("end_time") as end_time 
                from FDS_FDS_FILE_HISTORY 
                where "file_type" = 'update' 
                group by "table_name", "bundle", "text_file") s2 
            on s2."bundle" = ds."bundle" and s2."table_name" =  ds."table_name" and s2."text_file" = ds."text_file" 
Order by 
            ds."schema_name" asc, ds."bundle" asc, s2.end_time desc )

Expected result is i should not get the error and actual result is ===>when i run the same query in ORACLE SQL Developer it runs perfectly but does not run in excel VBA module.

Answer 1

despite of using to_date function

Actually it's because of using the to_date() function. You have things like this:

TO_DATE(SYSDATE,'DD-MM-YYYY')
TO_DATE(s2.end_time,'DD-MM-YYYY')
TO_DATE(next_day(SYSDATE,'SATURDAY'),'DD-MM-YYYY')
TO_DATE(next_day(SYSDATE,'SATURDAY')-(INTERVAL '7' DAY + INTERVAL '1' SECOND),'DD-MM-YYYY')

The second of those might be OK, but only if sd.end_time is stored as a string - which it almost certainly shouldn't be.

For the other three, at least, you're passing something that is already a date into a function that is there to convert from a string to a date. So you're really doing, for example:

TO_DATE(TO_CHAR(SYSDATE),'DD-MM-YYYY')

and as that implicit TO_CHAR() doesn't have a format model it will use your session's NLS settings, specifically NLS_DATE_FORMAT. You're seeing it work in one environment because the settings there effectively mean it's doing:

TO_DATE(TO_CHAR(SYSDATE, 'DD-MM-YYYY'),'DD-MM-YYYY')

whereas in another session it might effectively be doing:

TO_DATE(TO_CHAR(SYSDATE, 'DD-MON-RR'),'DD-MM-YYYY')

... which would lose the century, leaving a date value in year 0019 instead of 2019; or

TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM-DD'),'DD-MM-YYYY')

which throws "ORA-01861: literal does not match format string" as you are seeing.

While you could try to force the NLS settings on every client and application to match what you're doing it'll go wrong eventually, and you should be doing that conversion at all. Just use SYSDATE and other calculated directly instead; or if you're trying to get the dates with the times set to midnight truncate them:

trunc(SYSDATE)
trunc(s2.end_time)
trunc(next_day(SYSDATE,'SATURDAY'))
trunc(next_day(SYSDATE,'SATURDAY')-(INTERVAL '7' DAY + INTERVAL '1' SECOND))

Also note that the second argument to next_day() is the day name or abbreviation in the current session date language, so someone running this from a session in a different language will also see errors (like "ORA-01846: not a valid day of the week").