IBM's Sense of Humour

7 ^PM October 2, 2003

Leafing through the DB/2 SQL Reference, I found a function called to_date( string-expression, format-string ), which converts strings to dates. Just what I needed, I thought, for converting string in arbitrary formats to dates.

Nope.

This what the manual says about the format-string parameter:

format-string A character constant that contains a template for how the string expression is to be interpreted as a timestamp value. The length of the format string must not be greater than 254… The content of format-string can be specified in mixed case.

Valid format strings are: ‘YYYY-MM-DD HH24:MI:SS’

And that’s it. There is ONE valid format string. Passing any other value for that parameter earns you this error message:

SQL0171N The data type, length or value of argument “2” of routine “SYSIBM.TO_DATE” is incorrect. SQLSTATE=42815

Very funny, IBM.

By alang | # | Comments (5)

(Posted to Software Development and Rants)

Comments

At 06:40, 03 Oct 2003 wrote:

they obviously wanted to implement the format string mechanism later and didn't for whatever reasons, so what's the big deal ?

(#)

At 08:22, 03 Oct 2003 Joe Grossberg wrote:

Also possible:

1) You can use other formats, just not the ones you did. Just humor me and change that '24' to a '12'. ;)

2) It's deprecated by a more versatile date formatter, but is in there for backward compatibility.

(#)

At 08:55, 03 Oct 2003 Alan Green wrote:

Dear Mr Nobody:

You are right. They haven't implemented most of the functionality yet. The big deal is that they didn't make this clear in the manual. Instead they chose to dress the format-string defect as a feature with a few weasel-word paragraphs. I wouldn't mind except for the half-hour I spent chasing my tail.

Joe:

db2 => values to_date('1962-08-11 12:00:00', 'YYYY-MM-DD HH12:MI:SS')
SQL0171N The data type, length or value of argument "2" of routine
"SYSIBM.TO_DATE" is incorrect. SQLSTATE=42815

I think your #2 is on the money. The "one true" DB/2 to_date format string is also a legal Oracle to_date format string, I suspect that function is there for cross-database combatability reasons. There are many better ways to write literal dates in DB/2.

(#)

At 05:58, 23 Jun 2005 Brat wrote:

The to_date() function doesnt seem to work. I am getting the following error:<eb1>[DB2/AIX64] SQL0171N The data type, length or value of argument "2" of routine "SYSIBM.TO_DATE" is incorrect. SQLSTATE=42815
State: 42815, Native: -171, Source: IBM OLE DB Provider for DB2</eb1>.
Can someone out there please help.

(#)

At 09:03, 20 Oct 2005 yingyuheng wrote:

hi
  i want to delete the sysibm.to_date function ,how to do it?

(#)