SQL query fails, confused over leading character of 6 digit field

We are writing a PHP program to select our purchase orders based on a field in the header record. It's an old file, so the date is a six digit signed decimal field format MMDDYY. The A spec that defined it also uses a Y edit code.

A POENDT 6S 0 COLHDG('DATE' 'ENTERED') A EDTCDE(Y)

We want to select by year, so all we need are the last 2 characters of the field. This works ok for months 10, 11 and 12 when the field is full. For the other 9 months, when there is a zero in the first position, it gives fetch failure errors.



Select * from OHIOF.POHEAD P LEFT OUTER JOIN OHIOF.APVN01 V ON P.POVNBR = V.VNNBR WHERE VNNAME LIKE 'E.M.J. ' AND SUBSTRING(POENDT,5,2)=10 FETCH FIRST 40 ROWS ONLY



Viewing the file on the i, it clearly has a leading zero. But SQL is somehow missing this.

At some time in the future we will add a time/datestamp field, but for now, this is what we have to work with.



Any suggestions?

---Dale

IMPORTANT: This email is intended for the use of the individual addressee(s) named above and may contain information that is confidential, privileged or unsuitable for overly sensitive persons with low self-esteem, no sense of humor or irrational religious beliefs. If you are not the intended recipient, any dissemination, distribution or copying of this email is not authorized (either explicitly or implicitly) and constitutes an irritating social faux pas. Unless the word absquatulation has been used in its correct context somewhere other than in this warning, it does not have any legal or no grammatical use and may be ignored. No animals were harmed in the transmission of this email, although the terrier next door is living on borrowed time, let me tell you. Those of you with an overwhelming fear of the unknown will be gratified to learn that there is no hidden message revealed by reading this warning backwards, so just ignore that Alert Notice from Microsoft. However, by pouring a complete circle of salt around yourself and your computer you can ensure that no harm befalls you and your pets. If you have received this email in error, please add some nutmeg and egg whites, whisk and place in a warm oven for 40 minutes.