How to compare date to format date on oracle

Question

How can we compare a date to a format in Oracle? Something like this: if MyDate is on format DD MONTH YYYY THEN /....

elsif MyDate is on format YYYY-MONTH-DD Then...

EDIT: My dates are in varchar2 and i want to keep them that way. I want just to know how to write a regex that would reprensent for example 10 October 2010.

Is it possible ? If it is a regex how would its format be please

What do you mean? A DATE value in Oracle does not have any format. The (default) is just the **output** format. What is the data type of column `MyDate`? — Wernfried Domscheit, Dec 13 '18 at 11:11
Very bad idea when you say "dates are in varchar2 and I want to keep them that way" — Wernfried Domscheit, Dec 13 '18 at 12:05
Probable duplicate of https://stackoverflow.com/q/43633732/1509264 — MT0, Dec 13 '18 at 12:08

score 0 · Accepted Answer · answered Dec 13 '18 at 11:29

Echoing what was mentioned in the comments to your question, best practice would be to have an actual DATE type field instead of VARCHAR2, and if you needed specific display formats, store those in another field as a format pattern. That said, you can use REGEXP_LIKE to check the format using the patterns in the below example.

with dateinfo as (
    select 1 as id, '2018-MARCH-10' as dtString from dual
    union all
    select 2 as id, '10 MARCH 2018' as dtString from dual ) 
select id, dtString, 
    case 
        when regexp_like(dtString, '^[0-9]{4}-.[a-zA-Z]{3,}-.[0-9]{1,2}$') then 'format1'
        when regexp_like(dtString, '^[0-9]{1,2} [a-zA-Z]{3,} [0-9]{4}$') then 'format2'
        else 'no format'
    end as dtFormat 
from dateinfo;

How to compare date to format date on oracle

1 Answers1