CODEWITHSUNDEEP
sqloracle-databaseerror-handling
Caroline
Caroline

Reputation: 1

SQL ORA-01861: literal does not match format string

I have the following end of code on SQL (Oracle 19c)

WHERE DATE >= '20220101' AND DATE BETWEEN add_months(trunc(sysdate,'mm'),-1) AND last_day (add_months(trunc(sysdate,'mm'),-1)) AND DATE != 'None' AND QT_MOCK_DATA IS NOT NULL ORDER BY DATE DESC'

ERROR:

ORA-01861: literal does not match format string

Can someone help?

Thanks!

Upvotes: 0

Views: 393

Answers (3)

Don
Don

Reputation: 15

I don't know your DB's schema. Maybe try using date literal?

WHERE 
  DATE >= date '20220101' 
  AND DATE BETWEEN add_months(
    trunc(sysdate, 'mm'), 
    -1
  ) 
  AND last_day (
    add_months(
      trunc(sysdate, 'mm'), 
      -1
    )
  ) 
  AND DATE != 'None' 
  AND QT_MOCK_DATA IS NOT NULL 
ORDER BY 
  DATE DESC

Upvotes: 1

MT0
MT0

Reputation: 168671

Do not store dates as string, use a DATE data type (and do not use reserved words such as DATE as an identifier).

However, since you are storing the values as dates then convert the value you are comparing to to a string using TO_CHAR:

WHERE "DATE" >= '20220101'
AND   "DATE" >= TO_CHAR(add_months(trunc(sysdate,'mm'),-1), 'YYYYMMDD')
AND   "DATE" <  TO_CHAR(trunc(sysdate,'mm'), 'YYYYMMDD')
AND   "DATE" != 'None'
AND   QT_MOCK_DATA IS NOT NULL
ORDER BY "DATE" DESC

which can be simplified to:

WHERE "DATE" >= TO_CHAR(add_months(trunc(sysdate,'mm'),-1), 'YYYYMMDD')
AND   "DATE" <  TO_CHAR(trunc(sysdate,'mm'), 'YYYYMMDD')
AND   QT_MOCK_DATA IS NOT NULL
ORDER BY "DATE" DESC

(Since the start of the range will, assuming SYSDATE is not set to a past dates, always be greater than 20220101 and if the date range is valid then != 'None' will always also be true.)

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 143083

Column name certainly isn't date; it is reserved word, reserved for the datatype name. So, no - you don't have that code.

Next: it seems you are storing strings into that column (as you're comparing it to 'None', which is a string). That's a bad idea - dates should be stored as dates. Anything else brings problems (you hit one).

Then, you're comparing that string to date values. How come? The between part of the code returns date datatype values:

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> SELECT ADD_MONTHS (TRUNC (SYSDATE, 'mm'), -1) val1,
  2         LAST_DAY (ADD_MONTHS (TRUNC (SYSDATE, 'mm'), -1)) val2
  3    FROM DUAL;

VAL1                VAL2
------------------- -------------------
01.06.2022 00:00:00 30.06.2022 00:00:00

SQL>

Maybe you wanted to apply the TO_CHAR function with appropriate format model?

SQL> SELECT TO_CHAR (ADD_MONTHS (TRUNC (SYSDATE, 'mm'), -1), 'yyyymmdd') val1,
  2         TO_CHAR (LAST_DAY (ADD_MONTHS (TRUNC (SYSDATE, 'mm'), -1)),
  3                  'yyyymmdd') val2
  4    FROM DUAL;

VAL1     VAL2
-------- --------
20220601 20220630

SQL>

Because, with it, query returns something.

Compare

your code

SQL> WITH test (date1, qt_mock_data) AS (SELECT '20220615', 'A' FROM DUAL)
  2    SELECT *
  3      FROM test
  4     WHERE     date1 >= '20220101'
  5           AND date1 BETWEEN ADD_MONTHS (TRUNC (SYSDATE, 'mm'), -1)
  6                         AND LAST_DAY (ADD_MONTHS (TRUNC (SYSDATE, 'mm'), -1))
  7           AND date1 != 'None'
  8           AND qt_mock_data IS NOT NULL
  9  ORDER BY date1 DESC;
   WHERE     date1 >= '20220101'
             *
ERROR at line 4:
ORA-01861: literal does not match format string

to my code

SQL> WITH test (date1, qt_mock_data) AS (SELECT '20220615', 'A' FROM DUAL)
  2    SELECT *
  3      FROM test
  4     WHERE     date1 >= '20220101'
  5           AND date1 BETWEEN TO_CHAR (ADD_MONTHS (TRUNC (SYSDATE, 'mm'), -1),
  6                                      'yyyymmdd')
  7                         AND TO_CHAR (
  8                                LAST_DAY (ADD_MONTHS (TRUNC (SYSDATE, 'mm'), -1)),
  9                                'yyyymmdd')
 10           AND date1 != 'None'
 11           AND qt_mock_data IS NOT NULL
 12  ORDER BY date1 DESC;

DATE1    Q
-------- -
20220615 A

SQL>

Upvotes: 3

Related Questions