Clever Geek Handbook

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

I am trying to run the following db command for 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 am passing this as a string to db.ExecuteNonQuery method in MS Data Application block and getting the above error?

+2


source to share


4 answers


To get the date format '29 -08-1946 'to work, you will need the DBDATE environment variable to be set to a value such as "DMY4-" (or "DMY4 /"). These are the UK standard choices (I've used them for years, I now exclusively use "Y4MD-" which is both ISO 8601: 2004 (date formats) and ISO 9075 (SQL), except when debugging another environment) ... There are other environment variables that can affect date formatting - there are actually quite a few of them - but DBDATE takes precedence over others, so this is a big sledgehammer that fixes the problem.

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

WHERE dob = MDY(8,29,1946)

This works regardless of the DBDATE setting. Perhaps you can also use TO_DATE ():



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

Generated for me 1946-08-29 00: 00: 00.00000 - the function generates a DATETIME YEAR TO FRACTION (5) value, but they are reliably converted to DATE values ​​in Informix.

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

+5


source


Your date field is not formatted correctly. Since there is no 29th month in 1946, this is the cause of the error.



I would try just changing the month and day. 08-29-1946.

+1


source


How you read the daily and monthly portions of a date string may vary depending on your computer culture.

It is always safer to pass date strings to the database in the form "dd-MMM-yyyy" (ie "29 -aug-1946")

+1


source


Even safer to pass them as YYYY-MM-DD, dd-MMM-yyyy in this example won't work on a server with (for example) French.

+1


source






More articles:


All Articles