1

I'm having problem with the query to compare a column value type varchar2 with a value date string from input. But it always return an error Day of month must be between 1 and last day of month.... Especially when i add Order By clause behind the Where clause. I also check if the column is not equal '0'.

For example: *Table

+----+----------------------+
+ ID +  DATE_IN (VARCHAR2)  +
+----+----------------------+
+  1 + 01/04/2020           +
+----+----------------------+
+  2 + 02/04/2020           +
+----+----------------------+
+  3 + 04/04/2020           +
+----+----------------------+
+  4 + 08/04/2020           +
+----+----------------------+
+  5 + 0                    +
+----+----------------------+

  • INPUT String: '10/04/2020'

  • My query:

SELECT *
FROM `table_name`
WHERE DATE_IN IS NOT NULL
   AND DATE_IN <> '0'
   AND TO_DATE(DATE_IN,'DD/MM/YYYY') >= TO_DATE('10/04/2020','DD/MM/YYYY')
ORDER BY ID
Improve this question

4 Answers 4

Reset to default
1

There are values in your table that cannot be converted to dates. Validating date formats in a comprehensive manner is not an easy task. Oracle does not provide a built-in error handler for to_date(), so a typical solution is to create a validation function.

However, starting Oracle 12c release 2, this task gets far easier: you can use cast() with option on conversion error for that.

Here is a query that would pull out all invalid date strings from your table:

select * 
from mytable
where 
    date_in is not null 
    and cast(datein as date default null on conversion error, 'dd/mm/yyyy') is null

You can also use the same technique directly in your query:

select *
from mytable
where 
    cast(datein as date default null on conversion error, 'dd/mm/yyyy') 
        >= to_date('10/04/2020','dd/mm/yyyy')
order by id
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks for your reply, i'll try and tell you if it works :D
0

You have bad data -- which is why you don't want to store dates as strings.

In your case, you can look for it:

select date_in
from table_name
where substr(date_in, 4, 2) <= '00' or
      substr(date_in, 4, 2) > '12'

I would then recommend that you fix the data and change the data type to a date.

Improve this answer

1 Comment

Thanks for advising, i will check and fix it if i can
0

If all your values are actually valid date representations or zero, the issue is likely to be that Oracle is evaluating the to_date() call before it excludes the zeros. You can avoid that by moving the zero check into a case expression:

SELECT *
FROM table_name
WHERE TO_DATE(CASE WHEN DATE_IN <> '0' THEN DATE_IN END, 'DD/MM/YYYY')
  >= TO_DATE('10/04/2020','DD/MM/YYYY')
ORDER BY ID;

Storing dates as strings is not a good idea anyway, of course...

Improve this answer

2 Comments

When i put CASE WHEN into query, it causes another error SQL 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.
Although i've checked through all records, it's always dd/mm/yyyy
0

You can also avoid 0 or NULL values with a subquery like:

--this creates a dataset without 0 and null values
WITH t AS (
    SELECT *
    FROM table_name
    WHERE 
        date_in IS NOT NULL
        AND date_in <> '0'
)
-- this will select the values you require
SELECT *
FROM t
WHERE
    to_date(date_in, 'DD/MM/YYYY') >= TO_DATE('10/04/2020', 'DD/MM/YYYY')
ORDER BY id;
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.