Hi,

I am running some queries against an informix (2000?) database (from DTS via ODBC) that includes a DATE field in one of the tables.

I am not an informix expert, therefore, am I correct in thinking that when I query an Informix table, Informix only recognises dates in the format of mm/dd/yyyy or mm-dd-yyyy ?

e.g. there is not a differentiation between different locales? so no difference in US date format versus UK date format?

For example, if i wanted to extract some data for the date of 6th March 2006 I would do :

select *
from myTable
WHERE ROW_DATE = '03-06-2006' -- '06-03-2006' fails ?

Does this assumption hold true?

I noticed there was an informix function called MDY, so I thought it seemed strange that formats other than mm-dd-yyyy would be allowed.

Any help appreciated,

MrPeds

Hello MrPeds

I'am sorry I don't know what TDS is but anyway try the following. I assume you are using a Windows PC.
If you are running via ODBC you should have an IBM informix client connect installed on your system.
You should be able to find it under start->program on you Windows maskine.
Under Informix connect you should find a program called Setnet32. In this you can set different Informix Enviroment variables. Your need to find one called DBDATE. This enviroment variable controlls how dates are intrepreted between then client an your datebase server. Try to set this variable to md4y-. Also check the "use my settings". Click OK and try running your "TDS" again.
Hope this is helpfull
best regards
Thorkild

Thanks Thorkild,

My solution was quite simple in the end.

Firstly I performed a query against MS SQL Server to return the range of dates that  I am loading data for.  The format I return the data in is ODBC canonical format which is YYYYMMDD.

In my DTS package I then run a piece of ActiveX script to extract the day , month and year.

Finally I build a dynamic SQL string using the informix MDY function e.g. WHERE myDate = MDY(m, d, y)

m, d and y are the values i extracted in the ActiveX script.

MrPeds