I'm getting “Invalid month in date” trying to run this?

I'm trying to run the following db command against Informix:

delete from table1
    where u_id in (select u_id
                     from table2
                    where c_id in (select c_id
                                     from ptable
                                    where name = 'Smith'
                                      and dob = '29-08-1946'));

I pass this in as a string to the db.ExecuteNonQuery method in the MS Data Application block and I get the above error?

13.10.2009 15:20:03
I've tried passing the date in as '29/08/1946'::DATE and I stil get the Invalid month in date error?
Bernard 14.10.2009 10:13:25

To get the date format '29-08-1946' to work, you need your DBDATE environment variable set to a value such as "DMY4-" (or "DMY4/"). These are standard variations for the UK (I used them for years; I now use "Y4MD-" exclusively, which matches both ISO 8601:2004 (Date formats) and ISO 9075 (SQL), except when debugging someone else's environment). There are other environment variables that can affect date formatting - quite a lot of them, in fact - but DBDATE takes priority over the others, so it is the big sledgehammer that fixes the problem.

One of the problems is that your notation using a plain string is not portable between US and UK (and ISO) settings of DBDATE. If you have a choice, the neutral constructor for dates is the MDY() function:

WHERE dob = MDY(8,29,1946)

This works regardless of the setting of DBDATE. You can probably use TO_DATE() too:

SELECT TO_DATE('29-08-1946', '%d-%m-%Y') FROM dual;

This generated '1946-08-29 00:00:00.00000' for me - the function generates a DATETIME YEAR TO FRACTION(5) value, but those convert reliably to DATE values in Informix.

You can also use the DATE() function or an explicit cast to DATE (either CAST('29-08-1946' AS DATE) or '29-08-1946'::DATE), but both of those are subject to the whims of the locale of the users.

13.10.2009 22:25:25

Your date field is improperly formatted. Since there is no 29th month in the year 1946 that is what is causing the error.

I'd try just swapping the month and day. 08-29-1946.

13.10.2009 15:35:53

The way the day and month parts of a date string are read in can depend on your computer's culture settings.

It is always safer to pass date strings to a database in the form 'dd-MMM-yyyy' (i.e. '29-aug-1946')

13.10.2009 16:04:43
Or set up your connnection & code to use the ODBC format {d'1990-10-02'}. See msdn.microsoft.com/en-us/library/ms190234(SQL.90).aspx .
DaveE 13.10.2009 16:22:23

It's even safer to pass them as YYYY-MM-DD, the dd-MMM-yyyy in that example will fail on a server with a (for example) French locale.

13.10.2009 16:15:18