Progress OpenEdge 10.2A driver date comparison issue

[jerryb]

Can someone tell me how to properly construct a date compare using the Progress 10.2A driver in VB .Net 2010 program. The code below does not cause an error but the result set is not filtered by the date criteria. The field is a DATE type on the Progress database, I've tried all sorts of date formats, I hope someone here can assist. Thanks

select AdCharge.AdNumber, AdCharge.CustomerID, AdSchedule.BillDate, AdSchedule.PublicationID, AdSchedule.Linage, AdOrder.AdLine[1]
from PUB.AdOrder,PUB.AdSchedule,PUB.AdCharge
where AdSchedule.AdNumber = AdOrder.AdNumber
and AdCharge.AdNumber = AdOrder.AdNumber
and AdSchedule.PublishDate >= TO_DATE('05/01/2011') and AdSchedule.PublishDate <= TO_DATE('05/03/2011')

 

[Stefan]

Have you tried:

AdSchedule.PublishDate >= '2011-05-01' AND AdSchedule.PublishDate <= '2011-05-03'

 

[Marian EDU]

TO_DATE should work just fine, this is what you need to use anyway... however it might be that the Progress installation default date format could affect the default format of TO_DATE function as well, I've always though this is MM-DD-YYYY.

You might try to specify the date format just to be sure...

select * from PUB.order where orderdate >= to_date('01-21-1998', 'MM-DD-YYYY') and orderdate <= TO_DATE('23-01-1998', 'DD-MM-YYYY')

 

[jerryb]

Thanks for the replies. I've been at a loss so I removed the join condition from the query leaving just a simple query with the date condition... apparently the to_date has been working. FYI both these syntaxes work:
where AdSchedule.PublishDate = TO_DATE ('6/13/2011')

where AdSchedule.PublishDate = {d '2011-06-13'}

Now to figure out what's going on with this join, thanks for the help.