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
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