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?

Accepted Answer

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.

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

Solution

Problem