467,925 Members | 2,018 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,925 developers. It's quick & easy.

TO_CHAR(date-value, 'HH24') returns error: type, length or valueof arg is incorrect (42815)

I'm trying to convert some schema creation scripts that were
used on MS SQL Server to DB2 (v8.2) and have a problem with
the following select statement (snippet) used for a view:

SELECT
col1,
col2,
timestamp-col1,
TO_CHAR(timestamp-col1, 'HH24')
FROM ...

When creating the view that uses this statement, I get the following
error:
SQL0171N The data type, length or value of argument "2" of routine
"SYSIBM.TO_CHAR" is incorrect. SQLSTATE=42815


I tried using 'HH24:MI:SS', but this didn't help. The documentation
on the TO_CHAR() function (and TIMESTAMP_FORMAT) don't show any
examples of a format that doesn't include all timestamp parts (date
and time). However, this column needs to get only the hour portion.

Is there a way to do this in DB2 SQL?

Thanks,

--
Mark Hansen
Sacramento, CA
Nov 12 '05 #1
  • viewed: 9002
Share:
2 Replies
On 2/18/2005 08:49, Mark Hansen wrote:
I'm trying to convert some schema creation scripts that were
used on MS SQL Server to DB2 (v8.2) and have a problem with
the following select statement (snippet) used for a view:

SELECT
col1,
col2,
timestamp-col1,
TO_CHAR(timestamp-col1, 'HH24')
FROM ...

When creating the view that uses this statement, I get the following
error:
SQL0171N The data type, length or value of argument "2" of routine
"SYSIBM.TO_CHAR" is incorrect. SQLSTATE=42815


I tried using 'HH24:MI:SS', but this didn't help. The documentation
on the TO_CHAR() function (and TIMESTAMP_FORMAT) don't show any
examples of a format that doesn't include all timestamp parts (date
and time). However, this column needs to get only the hour portion.

Is there a way to do this in DB2 SQL?

Thanks,


After some more research, I found I can do this:

SELECT
col1,
col2,
timestamp-col1,
CAST(HOUR(timestamp-col1) AS CHAR),
FROM ...

Thanks for reading,
--
Mark Hansen
Sacramento, CA
Nov 12 '05 #2
Mark Hansen wrote:
I'm trying to convert some schema creation scripts that were
used on MS SQL Server to DB2 (v8.2) and have a problem with
the following select statement (snippet) used for a view:

SELECT
col1,
col2,
timestamp-col1,
TO_CHAR(timestamp-col1, 'HH24')
FROM ...

When creating the view that uses this statement, I get the following
error:
SQL0171N The data type, length or value of argument "2" of routine
"SYSIBM.TO_CHAR" is incorrect. SQLSTATE=42815 I tried using 'HH24:MI:SS', but this didn't help. The documentation
on the TO_CHAR() function (and TIMESTAMP_FORMAT) don't show any
examples of a format that doesn't include all timestamp parts (date
and time). However, this column needs to get only the hour portion.

Is there a way to do this in DB2 SQL?

db2 => values char(hour(current timestamp));

1
-----------
12

1 record(s) selected.
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Shimon Sim | last post: by
reply views Thread by jacksonmacd | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.