day of month must be between 1 and last day of month error-CodePudding

I write a code about to determine incorret data but I get error messages. Info about data, date columns have type of number, so I have to change them to date. Ex: 10322->01.03.2022

Here is my code;

select *
  from (select *****_date,
               *****_hold_no,
               case
                 when length(*****_end_date) = 5 then
                  to_date(lpad(*****_end_date, 6, '0'), 'ddmmyy')
                 else
                  to_date(*****_end_date, 'ddmmyy')
               end as *****_end_date,
               case
                 when length(*****_start_date) = 5 then
                  to_date(lpad(*****_start_date, 6, '0'), 'ddmmyy')
                 else
                  to_date(*****_start_date, 'ddmmyy')
               end as *****_start_date
          from ods_****.*****
         where *****_KW_GLAC = ' '
           and *****_KW_CAAC is not null) A
 where *****_end_date<*****_start_date

and it turns the error of "day of month must be between 1 and last day of month".

But if I change the condition of _end_date<_start_date to _end_date>_start_date, I do not get any error message and query works. What can be the problem? Thanks

Reason behind the error message

CodePudding user response：

Do not store dates as strings; change the table to store them as DATEs and then you can only store valid values and do not have to perform any type conversions.

However, since you have them as strings, don't use a CASE expression and instead, just use LPAD on all the values to ensure everything has the correct length (and, from Oracle 12.2, can use VALIDATE_CONVERSION to check that the can be converted to a date):

SELECT *
FROM   (
  SELECT obfuscated_date,
         obfuscated_hold_no,
         TO_DATE(LPAD(obfuscated_end_date, 6, '0'), 'ddmmyy') AS obfuscated_end_date,
         TO_DATE(LPAD(obfuscated_start_date, 6, '0'), 'ddmmyy') AS obfuscated_start_date
  FROM   ods_obfuscated.obfuscated
  WHERE  obfuscated_KW_GLAC = ' '
  AND    obfuscated_KW_CAAC is not null
  AND    VALIDATE_CONVERSION(LPAD(obfuscated_start_date, 6, '0') AS DATE, 'ddmmyy') = 1
  AND    VALIDATE_CONVERSION(LPAD(obfuscated_end_date, 6, '0') AS DATE, 'ddmmyy') = 1
) A
WHERE  obfuscated_end_date < obfuscated_start_date

You can also see the invalid values using:

SELECT obfuscated_date,
       obfuscated_hold_no,
       obfuscated_end_date,
       obfuscated_start_date
FROM   ods_obfuscated.obfuscated
WHERE  obfuscated_KW_GLAC = ' '
AND    obfuscated_KW_CAAC is not null
AND    (  VALIDATE_CONVERSION(LPAD(obfuscated_start_date, 6, '0') AS DATE, 'ddmmyy') = 0
       OR VALIDATE_CONVERSION(LPAD(obfuscated_end_date, 6, '0') AS DATE, 'ddmmyy') = 0
       )

CodePudding user response：

(at least) one of TO_DATE function calls fails because value you're working with is evaluated to invalid date.

For example, for January (which has 31 days), you got 32 or 56 or 93 or ...

That kind of problems happens when people store date values as strings - now you have to deal with that.

Error message looks like Oracle; you didn't specify which version you use. If possible, see on conversion error, such as

SQL> with test (datum) as
  2    (select '13.10.2022' from dual union all
  3     select '32.01.2023' from dual              --> invalid
  4    )
  5  select datum,
  6         to_date(datum default null on conversion error, 'dd.mm.yyyy') date_value
  7  from test;

DATUM      DATE_VALUE
---------- ----------
13.10.2022 13/10/2022
32.01.2023                  --> NULL, as DATUM is invalid

SQL>

Now it is easy to filter out values that can't be converted to dates.