I have table having date with two different formats in the same column. When I'm querying like extract(year from to_timestamp(colmnname, 'DD/MM/YYYY hh24:mi')) its giving me as date format picture ends before converting entire input string.
The type of format I have is like
1/1/2014 14:23
30/09/2014 12:31:11 PM
Ug. Storing dates in strings. This makes me sad :(
You need to use different masks depending on the date format.
Here's one way to do this with regular expressions:
create table t (
x varchar2(30)
);
insert into t values ('1/1/2014 14:23');
insert into t values ('30/09/2014 12:31:11 PM');
with converted as (
select case
when regexp_like(x, '(AM|PM)') then
to_timestamp(x, 'dd/mm/yyyy hh:mi:ss am')
else
to_timestamp(x, 'dd/mm/yyyy hh24:mi')
end ts
from t
)
select extract(year from ts) yr from converted;
YR
----------
2014
2014