|
|
|
|
|
|
| Author |
Message |
Mark Hansen
*nix forums beginner
Joined: 18 Feb 2005
Posts: 12
|
 Posted: Fri Feb 18, 2005 3:49 pm Post subject:
TO_CHAR(date-value, 'HH24') returns error: type, length or value of 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:
| Quote: | 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 |
|
| Back to top |
|
 |
Mark Hansen
*nix forums beginner
Joined: 18 Feb 2005
Posts: 12
|
| Posted: Fri Feb 18, 2005 4:18 pm Post subject:
Re: TO_CHAR(date-value, 'HH24') returns error: type, length or value of arg is incorrect (42815)
|
|
|
On 2/18/2005 08:49, Mark Hansen wrote:
| Quote: | 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 |
|
| Back to top |
|
|
Serge Rielau
*nix forums Guru
Joined: 29 Apr 2005
Posts: 1583
|
| Posted: Fri Feb 18, 2005 4:29 pm Post subject:
Re: TO_CHAR(date-value, 'HH24') returns error: type, length or value of arg is incorrect (42815)
|
|
|
Mark Hansen wrote:
| Quote: | 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 |
|
| Back to top |
|
|
Google
|
|
| Back to top |
|
|
|
|
The time now is Thu Jan 08, 2009 3:07 am | All times are GMT
|
|
Libro arquitectura | MPAA | Home Equity Loan | Debt Consolidation | Remortgages
|
|
Copyright © 2004-2005 DeniX Solutions SRL
|
|
|
|
Other DeniX Solutions sites:
Unix/Linux blog |
electronics forum |
medicine forum |
science forum |
|
|
Privacy Policy
|
Powered by phpBB © 2001, 2005 phpBB Group
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
Forum index
»
Databases
»
IBM DB2
