AS400 AND SQL SERVER 1

[dvannoy]

I am pulling records from an AS400 into a SQL server. I am trying to make the performance better in my SQL statements. I am using the Client Access ODBC driver and lined server within SQL server. the date field on our 400 is century date. so in my sql statement the only way I could get the records down to my sql server was to ask for DateField > 01030101 which is March 1st. as time goes on it starts to put a strain on my network. I want to only ask for the DateField = Date() Or Date -1..for todays and yesterdays records.. I have tryed the following :

DATE(DateField) = {FN CURDATE()})

everything I have tryed I get an ODBC error..

Can someone help me with this?

Thanks in Advance

 

[leveyp]

No problem. Try this

SELECT *
FROM yourlib.yourfile
where centdate + 19000000 >
year(curdate()- 10 days)*10000 +
month(curdate()-10 days)*100 +
day(curdate()-10 days)

 

[dvannoy]

DAM!!!!! Your good!.
Man, do you know how long I have been trying to find an answer to this?? it's been almost a year. couldn't even get an answer from IBM's web site or knowledge base..

would you mind if I wrote an FAQ on this and used your solution? there has got to be more people out there that have had this problem.

I REALLY appreciate this..

Thanks

 

[leveyp]

Feel free !!