select to_date ('2010 40 4','iyyy iw d') from dual;
ORA-01820: format code cannot appear in date input format
01820. 00000 - "format code cannot appear in date input format"
A date specification contained an invalid format code. Only the following
may be specified when entering a date: year, month, day, hours, minutes,
seconds, Julian day, A.M./P.M. and B.C./A.D.
select to_timestamp ('2010 40 4','iyyy iw d') from dual;
Surprise, no luck, the same error.
From Oracle support formely known as metalink can be found a statement that the feature has not been in such priority to be implemented. Build your own function. I am too lazy to do that. And as I know the timerange I am interested in I use brute force. Use data. We are in a database. It is built to store data. I will use that ability.
create table isoyearweekday_to_date(
isoyearweekday number(7) constraint isoyearweekday_to_date_pk primary key
, dat date not null)
organization index;
insert into isoyearweekday_to_date
select to_char(d,'iyyyiwd')
, d
from (
select to_date('17991231','yyyymmdd')+level d
from dual
connect by level <= to_date('22000101','yyyymmdd')-to_date('18000101','yyyymmdd')+1
)
;
select * from isoyearweekday_to_date where isoyearweekday = 2010404
;
2010404 07.10.2010
No comments:
Post a Comment