Solved

SUBSTR in WHERE clause SQl/DB2

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

863 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

23 Experts available now in Live!

Get 1:1 Help Now