2010-10-07

ISO year week day to date

Trying to get date out of three of numbers. Three numbers are ISO standard year, ISO standard week and number of a day in a week. The first day of a week is Monday.


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

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.