troubleshooting Question

DB2 Select Statement for Ticket Date

Avatar of DFCRJ
DFCRJFlag for United States of America asked on
DB2
5 Comments1 Solution2819 ViewsLast Modified:
I'm connecting to an IBM AS 400 using 5.4 client access drivers and using vs2005 to design my application. I'm copying data from the 400 to my sql server. Below is my select statement.

SELECT  CUSTNO, CUSTMRNAME, CUSTMRADDR1, CUSTMRZIPCOD, LASTPAYAMT,  ((PMTCN * 1000000) + (PMTYR * 10000) + (PMTMO* 100) + (PMTDA))  AS PaymentDate            
FROM         CUSTOMERS.CUSMAS
WHERE   ((PMTCN * 1000000) + (PMTYR * 10000) + (PMTMO* 100) + (PMTDA)) >= 20071021

My problem occurs once I try to move the date over to my sql 2005 in a table called Customers. All the fields copy over except the Paymentdate when the destination field is defined as a Date. If I define my destination field as varchar it works but the data is of course not in the right format. How can I can change the source fields to represent dates and get my WHERE clause to work?
I've even tried it this way:

DATE(digits(CM_PMT_MO)||'/'||digits(CM_PMT_DA)||'/'||digits(CM_PMT_CN)||digits(CM_PMT_YR)) as PayDate

but then my WHERE clause doesnt filter anything since you cant filter on the defined Paydate field.
basically, I'm wondering how can I get the defined field PayDate into a date value .

Any help would be appreciated. thx




ASKER CERTIFIED SOLUTION
momi_sabag

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros