Re: SQL error using ifxoledbc

On Wed, 2008-02-13 at 12:25 -0800, JTG wrote: > On Feb 6, 3:37 am, Richard Spitz <Richard.Sp...@med.uni-muenchen.de> > wrote: > > JTG <jgaf...@smgnj.com> schrieb: > > > > >ifxoledbc.dll version: 2.90.TC3 > > >DB Version: DS 9.40.FC7 > > > > >I'm unable to get the following SQL to run using the Informix OLEDB > > >provider: > > > > >SELECT DATE('01/01/'||YEAR(onset_date)+1) dt1, birthdate as dt2 > > > FROM customer > > > WHERE custID = 403682 > > > AND MONTH(birthdate) < 12 > > >GROUP BY 1,2 > > > > Sorry for the late reply. OLEDB expects its dates formatted as > > "YYYY-MM-DD", you cannot use anything else. I had the same problem > > as you a couple of years ago when first starting to use OLEDB, and > > it took me some time to figure this out. > > > > I've also set "DBDATE" to "Y4MD-" in SetNet32, since then everything > > works smoothly. > > > > So your query should work if you write it like this: > > > > SELECT DATE(YEAR(onset_date)+1||'01/01/') dt1, birthdate as dt2 > > FROM customer > > WHERE custID = 403682 > > AND MONTH(birthdate) < 12 > > GROUP BY 1,2 > > > > HTH, Richard > > Thanks man! -jtg > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list I'd suggest using MDY as general rule. We have a third party system which likes to use string values for dates. The date strings cause all kinds of trouble if we call code containing them in a different context. It gets extremely annoying tracking down what is actually the problem. -- -------------------------------------------- Chris Salch Programmer/Analyst LeTourneau University