Oracle :: Error ORA-01858

Question

Oracle :: Error ORA-01858

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I have a table in oracle with a text type column. This column stores dates in dd / mm / yyyy format and may or may not be filled.

I need to filter in that table and show only the records that have that field filled with a date less than or equal to today.

I've tried with this

select txt from table where txt is not null and type = 'date'
and TO_DATE(txt, 
'DD/MM/YYYY') <= trunc(sysdate)

and with this

select * from (
       select txt from table where txt is not null and type = 'date'
) t where TO_DATE(t.txt, 'DD/MM/YYYY') <= trunc(sysdate)

and in both cases I get an error ORA-01858.

How do I do it then to be able to filter in that way ??? in that field I can store information of different types: integers, dates, strings ... from there I filtered through the column where I indicate the type 'date'

oracle

asked by Alejandro 19.07.2018 в 13:49

source

2 answers

Problem when updating a record Folder for MySSQL database in Tomcat

score 0 · Answer 1

First you have to check if the value of that field is a valid date.

You can create a function to check if it's a date:

create function test_date(d varchar2) return integer
is
  v_date date;
begin
  select to_date(d,'DD/MM/YYYY') into v_date from dual;
  return 1;
  exception when others then return 0;
end;

Now you only have to use it in your query:

select txt from (
   select txt from table where test_date(txt) = 1
) t where TO_DATE(t.txt, 'DD/MM/YYYY') <= trunc(sysdate)

Source

Edit

It seems that sometimes you can execute the external part of the query before the internal, for those times (or for all) better

create function test_date(d varchar2) return date
is
  v_date date;
begin
  select to_date(d,'DD/MM/YYYY') into v_date from dual;
  return v_date ;
  exception when others then return NULL;
end;


select txt from (
   select test_date(txt) as txt from table where test_date(txt) != NULL
) t where t.txt <= trunc(sysdate)

score 0 · Answer 2

If you try to remove that type, you are already doing the casteos:

    select txt from (select txt from table where txt is not null and trim(txt) != '')
     where TO_DATE(txt, 
    'DD/MM/YYYY') <= trunc(sysdate)

I hope it serves you!