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

Question

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?

Answer 1

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.

Answer 2

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')

Answer 3

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.

Answer 4

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.