literal does not match format string-version 11

Question

In my SQL query I have,

TO_DATE('2019-07-22' , 'YYYY-MM-DD')

I want to pass sysdate-1 instead of hard coding the date value.

I tried this,

select TO_CHAR(to_date(
sysdate-1,'DD-Mon-YY'),'YYYY-MM-DD')dates from dual

and replaced the same in the SQL query but am getting the below error,

ORA-01861: literal does not match format string 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.

Replace the entire thing simply with sysdate - 1. No need for any to_date, to_char, or any format models. — mathguy
NEVER, ever call to_date() on a value that is already a date (which sysdate is). That will first convert the date value to a varchar just to convert that varchar back to a date which it was to begin with. — a_horse_with_no_name
@Winnieg You'd probably want to use trunc(sysdate) etc, rather than just sysdate (unless the you want the time to be used as well) — Boneist

Littlefoot Littlefoot · Accepted Answer · 2019-08-02T06:10:25

Sysdate is a function that returns DATE datatype; you don't TO_DATE it. Therefore:

SQL> select to_char(sysdate - 1, 'yyyy-mm-dd') result from dual;

RESULT
----------
2019-08-01

SQL>

If you meant to use date datatype, then - as comments suggest - there's no need for any TO_something function:

SQL> select sysdate as today,
  2         sysdate - 1 as yesterday
  3  from dual;

TODAY    YESTERDA
-------- --------
02.08.19 01.08.19

SQL>

The result of such a subtraction is date datatype. The way it is presented to you depends on NLS settings; in my database, it is set to dd.mm.yy. In order to present it differently, you'd either modify settings or apply to_char with appropriate format mask, e.g.

SQL> alter session set nls_date_format = 'yyyy-mm-dd';

Session altered.

SQL> select sysdate as today,
  2         sysdate - 1 as yesterday
  3  from dual;

TODAY      YESTERDAY
---------- ----------
2019-08-02 2019-08-01

SQL>

literal does not match format string-version 11

2 Answers