How to extract date of particular format in Oracle?

Question

I have this table where VARCHAR2(128 CHAR) field could be of the format 2018-01-01 00:00:00 or 01/01/2018 00:00:00 or 01-JAN-2018 00:00:00

How can I pull records that has the VARCHAR2(128 CHAR) field in 2016-01-01 00:00:00 or 01-JAN-2018 00:00:00 format alone?

Why on earth are you storing date values in a `VARCHAR` column? — , Mar 07 '18 at 13:11
Even I'm stuck with the same. Every date field in every table uses varchar as the datatype in the existing module ! — Mike, Mar 07 '18 at 13:20
Indeed? Oracle version 1.5.5. does not exist, it was an experimental database management system that was never released to the public. The first public Oracle version was Oracle V2 - released 1979! — Wernfried Domscheit, Mar 07 '18 at 13:29
I think @KaushikNayak wanted to know the version of the oracle DB. You can get it with SELECT * FROM V$VERSION — Chrᴉz remembers Monica, Mar 07 '18 at 13:29
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production — Mike, Mar 07 '18 at 13:36
I wanted to know the version because in Oracle 12.2 there is a handy `TO_DATE` function with `DEFAULT ON CONVERSION ERROR` clause : https://oracle-base.com/articles/12c/conversion-function-enhancements-12cr2 which could simplify the solution. — Kaushik Nayak, Mar 07 '18 at 15:28

score 1 · Accepted Answer · answered Mar 07 '18 at 13:21

Not good storing a date in a varchar. But as it is like this, I would propose converting the string to a date and then back to string again. Like so:

declare
  d date;
begin
  begin
    d:=to_date('01.01.2011 23:49:00','dd/mm/yyyy hh24:mi:ss');
  exception
    when others then
      begin
        d:=to_date('01.01.2011 23:49:00','dd.mm.yyyy hh24:mi:ss');
      exception
        when others then
          begin
            d:=to_date('01.01.2011 23:49:00','dd-mm-yyyy hh24:mi:ss');
          exception
            when others then
              -- any more date format strings?
              null;
          end;
      end;
  end;
  dbms_output.put_line(to_char(d,'dd-mm-yyyy hh24:mi:ss'));
end;
/

wolfrevokcats · Answer 2 · 2018-03-07T15:45:45.043

Create a function that converts a date string to date:

create or replace function date_from_string(str varchar2, fmt varchar2) return date is
    v date;
begin
    return to_date(str,fmt);
exception when others
    then return null;
end;

And query the table, for example:

with t as (
select case when regexp_like(field, '^\d{4}-\d\d-\d\d \d\d:\d\d:\d\d$') 
              then 'YYYY-MM-DD HH24:MI:SS'
            when regexp_like(field, '^\d\d-[a-zA-Z]{3}-\d{4} \d\d:\d\d:\d\d$') 
              then 'DD-MON-YYYY HH24:MI:SS'
             end fmt, t.*
from "TABLE" t
)
select t.*, date_from_string(t.field, t.fmt) "DATE" from t
where date_from_string(t.field, t.fmt) is not null;

This way you can easily check that the date in a given record is correct.

How to extract date of particular format in Oracle?

2 Answers2