Wednesday, April 28, 2010

ORA-01820: format code cannot appear in date input format

I learned something new today...you can't use week date format qualifiers in TO_DATE.

I was trying to transform a week to a month like this:
CJUSTICE@TESTING>SELECT TO_DATE( 200803, 'YYYYWW' ) FROM DUAL;
SELECT TO_DATE( 200803, 'YYYYWW' ) FROM DUAL
*
ERROR at line 1:
ORA-01820: format code cannot appear in date input format
Then tried one "W"
  1* SELECT TO_DATE( 200803, 'YYYYW' ) FROM DUAL
CJUSTICE@TESTING>/
SELECT TO_DATE( 200803, 'YYYYW' ) FROM DUAL
*
ERROR at line 1:
ORA-01820: format code cannot appear in date input format
Then "IW"
  1* SELECT TO_DATE( 200803, 'YYYYIW' ) FROM DUAL
CJUSTICE@TESTING>/
SELECT TO_DATE( 200803, 'YYYYIW' ) FROM DUAL
*
ERROR at line 1:
ORA-01820: format code cannot appear in date input format
Datetime Format models in 10gR2

Lo and behold the Error messages manual didn't have it (at least where I would expect it).



Put it up on twitter and Gary Myers responded:



That's good to know.

Now I just need to figure out how to transform that week into a month.

I'm trying to generate some test data for a quick...test.

I switched to using SYSDATE as that would allow more more flexibility to transform the other way.
SELECT DISTINCT
TO_NUMBER( TO_CHAR( r, 'YYYYWW' ) ) fiscal_year_week,
TO_NUMBER( TO_CHAR( r, 'YYYYMM' ) ) fiscal_year_month
FROM
(
SELECT SYSDATE - rownum r
FROM dual
CONNECT BY LEVEL <= SYSDATE - TRUNC( SYSDATE, 'YEAR' )
)
Which gave me exactly what I needed
FISCAL_YEAR_WEEK FISCAL_YEAR_MONTH
---------------- -----------------
201015 201004
201014 201004
201012 201003
201011 201003
201007 201002
201006 201002
201002 201001
201016 201004
201010 201003
201003 201001
201017 201004
201013 201003
201009 201003
201009 201002
201005 201002
201004 201001
201013 201004
201008 201002
201005 201001
201001 201001

10 comments:

Noons said...

If you really wanted to use week number, this is a possible workaround:
SQL> select to_date(17*7,'DDD') from dual;

TO_DATE(17*7,'DDD')
--------------------
2010-APR-29 00:00:00

1 row selected.
In a nutshell: multiply the week number by 7 and flip that back to a date using the 'DDD' (day of year) converter code.

Then you could use the last conversion in your example to bring it back as a YYYYMM using a to_char, which accepts all the codes.

Go to
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34924
and scroll down a bit to see all possible combinations of codes and which can be used in a to_date function - the ones with "yes" in the second column of the table.

oraclenerd said...

@noons,

Ah...I see that column now. I didn't read closely enough.

That's an interesting method...never thought of it that way. I enjoy working with date math (now anyway, I used to hate it).

Would it be safe to say that you were working with Oracle before the easy date match stuff? Or has it always been there?

moleboy said...

I'm not sure I'd be happy with any requirement for turning a week into a month.
Weeks don't translate to months, regardless as to Oracle's handling.

week 05 can be in both January and February. You could pick the first day of the week, but that will not work in Noons' solution (which will get you the last day of the week).

To further complicate things, if you chose either the first day or the last day, that day might be the only one in that month.

Just saying, it is a bad/misleading conversion no matter what.

Noons said...

Actually there is nothing "bad" or "misleading" about it.

It picks a very precise day - the end of a given week - and produces the month and year it belongs to. If you want the beginning of the week instead, that is also possible and precise.

A week is defined either by its start or end, not by its middle given that it does not represent a single day.

And of course it can span a month. A week is a span of time that is not a submultiple of a month. So is a fortnight. If it didn't, that would indeed be misleading!

That is an issue with day calendars and weeks, not a problem with the conversion Oracle performs.

As such, I find the "bad and misleading" comment totally out of of context.

But then again, we all know there is an urgent need from certain sectors for classing anything I say as "misleading", isn't it?

Funny thing: it's always done by someone using a fake id or who never posted before. I wonder why?...

But keep it up: I'm quite sure there is an Oracle Ace award or something else equally irrelevant awaiting!

Anyways, @chet: it's been there for a long time AFAIK. Date arithmetic and conversions have always been a very strong part of Oracle, at least since I started using it in release 4. There is some new stuff at about release 8, and of course the new timestamp data type - *there* is a good example of a confusing variation! - but otherwise it's been pretty much a constant.

Quite handy: there is indeed some pretty fancy stuff that can be done with it.



Just as a humurous aside:

Of course those who agonize over "which precise day of the tenth millenium are we in now and how it matches the Vatican's meaning of history" will have something negative to say about Oracle's date handling.

The whole field of dates and time measurement is a universe in itself, with multiple interpretations.

Hardly the point with Oracle, though: it's a database, not a time-keeping research tool.

Although of course: Ingres, Postgres and DB2 do it much better.

Yes.

Go away, now!
.
.
.

oraclenerd said...

@noons

I would have to agree...just tell me how you want the dates and let me go from there (Julian, ISO, etc). I am not a philosopher (IANAP?). ;)

Gary Myers said...

@Noons,
"I'm quite sure there is an Oracle Ace award or something else equally irrelevant awaiting!"
Not a nice thing to say on the blog of an Oracle ACE :)

moleboy said...

wow, that was reasonably uncalled for. You'll notice I wasn't commenting about the code, or about oracle, but rather the requirement itself:"I'm not sure I'd be happy with any requirement for turning a week into a month."

Also note that I didn't make the attack personal.

Finally, while I don't post here often, I have before.

So, in summary, what, exactly, is your issue?

tomcatkev said...

LOL, I think it is a perfectly good question "why do you want to turn a week into a month" and certainly it is relevant to consider which is more relevant for this reporting the start or end of the week.

I have to deal with on-call reporting paging logic that breaks the week at 9am on Monday, so prior to 9am last week's dba gets called, and after 9am we rotate the next dba in the call rotation. It would be easier of course to have assumed that the week starts at 00:00 on the 0 day Sunday, but since this isn't a work time there is no good reminder of the handoff.

Anyway, as long as you and your customer agree on "what is a week" you are good, but just need to take care in what is being assumed.

Take care

Joel Garry said...

I've seen legit requirements both in payroll and GL like this. Some GL systems have completely arbitrary weeks or months. Most allow system date to vary from period date, often between different finance modules too.

As far as first or last day, it is common to have paydays and payroll release dates a couple of days apart in the middle of the week, and different payroll days for different types of pay.

And that's just the US!

The requirements are often only stupid when stated baldly out of context. They may be stupid in context too, but if the context involves government regulations, accounting standards bodies, auditors and history, good luck saying that out loud.

If you're not happy about functional analysis, go do something else.

moleboy said...

Actually, Joel, 90% of my work is on government contracts. I feel pretty strongly that I get paid to tell them when a requirement seems, shall we say, sketchy.
I may get told to do it anyways, but I wasn't hired to keep quiet.