Solved

SUBSTR in WHERE clause SQl/DB2

Posted on 2011-09-13
5
9,590 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!



0
Comment
Question by:Mark1M
5 Comments
 
LVL 24

Expert Comment

by:jimyX
ID: 36534036
What is the data type of ldate & mdate?
0
 
LVL 42

Expert Comment

by:dqmq
ID: 36534188
just guessing, probably because substr doesn't work on dates.  Try casting/converting to a string before applying the substr function.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 36534208
WHERE
substr(cast(ldate as varchar(26)),1,4) = substr(cast(mdate as varchar(26)),1,4)
0
 
LVL 45

Accepted Solution

by:
Kdo earned 250 total points
ID: 36535961
Hi Mark,

What's the data type of ldate and mdate?  If they are date and/or timestamp objects, you can use any of the built-in DB2 date functions to extract the portion of the data that you want.  

Since you're extracting 4 characters, I'll assume that you're comparing years.  In DB2, the equivalent SQL would be:

  WHERE year (ldate) = year (mdate)

And of course, if they are really strings, dqmq has posted a definitive answer.  :)


Good Luck,
Kent
0
 

Author Closing Comment

by:Mark1M
ID: 36536556
Kent & Everyone,
Thank you!
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now