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

[Mark Hansen]

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

[Mark Hansen]

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

[Serge Rielau]

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