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!

CodePudding user response：

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>

CodePudding user response：

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.)