Is there a way to select the strings '03/01/2018' and '09-NOV-17' so that the results are both in 'MM/DD/YYYY' format?
The result can also be a string.
2 Answers
No need to use regular expression - a plain LIKE is fine to do the selection of the DATE format.
Example
with my_data as (
select '03/01/2018' dt from dual union all
select '09-NOV-17' from dual)
select
dt,
case when dt like '__/__/____' then to_date(dt,'dd/mm/yyyy')
when dt like '__-___-__' then to_date(dt,'dd-mon-rr')
end as dt_date
from my_data;
.
DT DT_DATE
---------- -------------------
03/01/2018 03.01.2018 00:00:00
09-NOV-17 09.11.2017 00:00:00
Comments
You can use a case expression. Assuming all values are in one of the two formats, you can convert to a date by doing:
select (case when col like '%-%-%' then to_date(col, 'MM/DD/YYYY')
else to_date(col, 'DD-MON-YY')
end)
I prefer the value in date format. You can use to_char() to convert to a string format of your choosing.
2 Comments
Wernfried Domscheit
I never heard a regular expression
'%-%-%', can you please elaborate? Perhaps you mean LIKE '%-%-%'
Gordon Linoff
@WernfriedDomscheit . . . Silly me. I was going to use a regular expression to look for the right format. Then I realized that
like would do, because of the restriction on the value having one of the two formats. I changed it to like.