所以我正在尝试将一年中的某些日期转换为日期格式。问题是一年中的某些日期对于 100 以下的日子采用 DDYYYY 格式,对于第 100 天及以上,日期采用 DDDYYYY 格式。我尝试了以下方法,但仍然收到"一年中的某一天必须在 1 到 365 之间(闰年为 366("错误:
select CASE when data_Date >= 999999
then to_date(data_date, 'DDDYYYY')
when data_Date >= 99999
then to_date(data_Date, 'DDYYYY')
else to_date(data_date, 'DYYYY')
END as DATA_DATE_CONVERTED
from table;
提前致谢
示例数据如下:
Data_date (being passed in as a varchar2)
1072015
12017
612014
672013
72017
1112018
最后 4 位数字是年份。使用 LPAD
在 DAY 上放置前导零
select to_date( lpad(dayyear, 7, '0'),'DDDYYYY')
from table;
SQLfiddle
这个怎么样:
with demo (data_date) as
( select 1072015 from dual union all
select 12017 from dual union all
select 612014 from dual union all
select 672013 from dual union all
select 72017 from dual union all
select 1112018 from dual )
select data_date
, to_char(data_date,'0000000')
, to_date(to_char(data_date,'0000000'),'DDDYYYY') as data_date_converted
from demo
您也可以在两者之间加上破折号 (-(。
with demo (data_date) as
( select 1072015 from dual union all
select 12017 from dual union all
select 612014 from dual union all
select 672013 from dual union all
select 72017 from dual union all
select 1112018 from dual )
select data_date, TO_DATE(SUBSTR(data_date, 1, length(data_date)-4)||'-'||SUBSTR(data_date, -4, 4), 'DDD-YYYY')
from demo;