Take the 2-minute tour ×

I have a linked server setup in SQL Server 2008R2 (10.5) connecting to an Informix (Atomix) database using the Microsoft OLE DB Provider for ODBC Drivers (which is really pointing at a DSN that uses an ODBC driver). Through this, I can insert records as long as the record I'm inserting does not try to insert a date value. It doesn't matter the delimiter I use around the date value nor the SQL syntax I attempt -- see examples:

INSERT INTO  [linkedinformix]...[tablename](daterequested) VALUES (2013-06-27) 

SELECT * FROM OPENQUERY(linkedinformix,'INSERT INTO tablename (daterequested) 
VALUES (2013-06-21))

The above will give syntax error or a type clash error (or in other cases, if I don't run the provider out of process, will crash SQL Server). I've tried using {}, #, |, and other delimiters around the date value I'm passing and also tried different date formats (06/27/2013, etc.).

If I point Microsoft Access at that same DSN to create a linked table, I can manually write dates to the table, so I know the ODBC driver can handle it.

There must be a simple answer...

share|improve this question
What is the value of the Informix DBDATE environment variable? –  FrankComputerAtYmailDotCom Jul 14 '13 at 5:08
add comment

2 Answers

up vote 1 down vote accepted

Informix and DATE types — a fun (complicated) topic. Actually, it is fairly simple from within the Informix world; it is when other systems get involved with different views on how things should be done that it gets tricky.

If things are set up correctly (e.g. as in my environment), you could write in the Informix world:

INSERT INTO tablename(daterequested) VALUES('2013-07-03');

And you could substitute double quotes for single quotes because Informix is lax about the difference unless you hold its hand to the fire and say "I want to be shouted at when I use double quotes".

More elaborately, you could also write:

INSERT INTO tablename(daterequested) VALUES(DATETIME(2013-07-03) YEAR TO DAY);

This will work because (a) the formats for DATETIME are fixed in ISO 8601 (Date/Time Formats) or ISO 9075 (SQL) format and (b) Informix will convert from DATETIME YEAR TO DAY to DATE without any qualms. This is reliable, and doesn't rely on any environment variable settings or other complications, unlike the first version.

You could also reliably write:

INSERT INTO tablename(daterequested) VALUES(MDY(7, 3, 2013));

This uses a function MDY to convert the three integers to a DATE; the order of the arguments is (mnemonically) month, day, year. This is reliable because it doesn't depend on environment variables.

The first notation (using string '2013-07-03') relies on environment variables. The classic variable is $DBDATE; I run with DBDATE=y4md- set in the environment, so strings like '2013-07-03' are interpreted as in ISO 9075. However, the default value for DBDATE is effectively DBDATE=mdy4/ for US-style dates. However again, there are other variables, such as CLIENT_LOCALE, DB_LOCALE, and GL_DATE that all want to get in on the game. I use DBDATE because it gets top priority (and has done since the beginning of time), but the newer (other) variables have their merits. You can also experiment with:

INSERT INTO tablename(daterequested) VALUES(DATE('07/03/2013'))

Note the quotes and the parentheses. The string is interpreted according to the environment variables. Don't try DATE(2013-07-03) because that is equivalent to DATE(2003) (2013 minus 7 is 2006; 2006 - 3 is 2003), and because day 1 was 1900-01-01, day 2003 was 1905-06-16, a Monday.

The SQL standard provides for DATE '2013-03-07' but Informix does not support that parentheses-less notation.

You'll need to stick the SQL Server notations back into the SQL syntax, but the MDY and DATETIME methods will work, and you can finagle the DATE methods into working if you're willing to work with the environment variables or modify the format of the date string to match the expected behaviour.

share|improve this answer
Thanks, but none of these worked in my situation. I suspect it's the older version of Informix (Atomix, used by Noble Systems) and/or a proprietary ODBC driver that's the real issue (though MS Access can insert dates somehow when connected through ODBC). –  Daryl Jul 11 '13 at 15:43
If the MDY() variant doesn't work, I'm astonished. The DATETIME(...) YEAR TO DAY variant should work too. For those to fail, the driver has to be messing up -- actively, even if not deliberately, breaking -- the SQL that is being sent to Informix. Any of the variants involving string literals could be a problem. Maybe you should use SET EXPLAIN ON and see what the Informix server is being sent as the SQL to process, though that may not help if you are getting syntax errors (it can only explain that which is executed). I do have code to monitor raw SQL conversations; it is not trivial to use. –  Jonathan Leffler Jul 11 '13 at 16:18
Thanks. I marked your answer as accepted, as it's fairly definitive, though it didn't work in my particular situation (probably because Atomix is an offshoot of an older Informix). –  Daryl Aug 12 '13 at 15:25
add comment

Try the format Mmm-DD-YY as per this answer:

Linked Informix table in MS SQL Server ignoring criteria

Note that it's case sensitive.

share|improve this answer
Already tried that ... it returns "(-201)A syntax error has occurred." –  Daryl Jun 28 '13 at 13:16
add comment

Your Answer


