We help IT Professionals succeed at work.

SUBSTR in WHERE clause SQl/DB2

Mark1M
Mark1M asked
on
15,595 Views
Last Modified: 2012-05-12
Hi,
I am using SUBSTR in a WHERE clause in an SQL DB2.statement.
SYNTAX:

WHERE
substr(ldate,1,4) = substr(mdate,1,4)

I get the following error.
"SELECT - 0 row(s), 0.000 secs]  [Error Code: -440, SQL State: 42884]  DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=SUBSTR;FUNCTION, DRIVER=3.57.82"

Is anyone familar with this?

Thanks in advance!



Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2011

Commented:
What is the data type of ldate & mdate?

Commented:
just guessing, probably because substr doesn't work on dates.  Try casting/converting to a string before applying the substr function.

Commented:
WHERE
substr(cast(ldate as varchar(26)),1,4) = substr(cast(mdate as varchar(26)),1,4)
Data Warehouse / Database Architect
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
Kent & Everyone,
Thank you!