Informix Error 1204
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up I'm getting “Invalid month in date” trying to run this? up vote 2 down vote favorite 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? informix share|improve this question edited Oct 14 '09 at 0:55 Jonathan Leffler 440k62511823 asked Oct 13 '09 at 15:20 Bernard 6031925 I've tried passing the date in as '29/08/1946'::DATE and I stil get the Invalid month in date error? –Bernard Oct 14 '09 at 10:13 add a comment| 4 Answers 4 active oldest votes up vote 4 down vote accepted 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. share|improve th
error 1205 - Invalid http://www-01.ibm.com/support/docview.wss?uid=swg21382114 month in date. Technote (FAQ) Question How to resolve 1205 errors when attempting a select, update or insert on a DATE column. https://www.postgresql.org/docs/9.1/static/ecpg-informix-compat.html Cause The SQL is of one format (eg. DMY4) but the DBDATE environment variable is set to another format (eg. MDY4). Example: informix error export DBDATE=DMY4 select * from mytable where date_column = '05/20/1998' Will cause error 1205. Answer Set the DBDATE environment variable to match the format of the SQL being run or change the SQL to match the DBDATE format. Example (from above): 1. Run informix error 1204 "export DBDATE=MDY4" from the command line or 2. Change the SQL to: select * from mytable where date_column = '20/05/1998' IBM Informix products use the following precedence to determine the end-user format for an internal DATE value: 1. DBDATE 2. GL_DATE 3. Information defined in the client locale (if CLIENT_LOCALE is set) 4. Default date format is %m/%d/%iy (if DBDATE and GL_DATE are not set, and no locale is specified) Document information More support for: Informix Servers Software version: 11.5, 11.7, 12.1 Operating system(s): AIX, HP-UX, Linux, Solaris, Windows Software edition: Developer, Enterprise, Express, Growth, Ultimate, Workgroup Reference #: 1382114 Modified date: 2015-06-05 Site availability Site assistance Contact and feedback Need support? Submit feedback to IBM Support 1-800-IBM-7378 (USA) Directory of worldwide contacts Contact Privacy Terms of use Accessibility
9.1.23 Documentation Prev Up Chapter 33. ECPG - Embedded SQL in C Next 33.15. Informix Compatibility Mode ecpg can be run in a so-called Informix compatibility mode. If this mode is active, it tries to behave as if it were the Informix precompiler for Informix E/SQL. Generally spoken this will allow you to use the dollar sign instead of the EXEC SQL primitive to introduce embedded SQL commands: $int j = 3; $CONNECT TO :dbname; $CREATE TABLE test(i INT PRIMARY KEY, j INT); $INSERT INTO test(i, j) VALUES (7, :j); $COMMIT; Note: There must not be any white space between the $ and a following preprocessor directive, that is, include, define, ifdef, etc. Otherwise, the preprocessor will parse the token as a host variable. There are two compatibility modes: INFORMIX, INFORMIX_SE When linking programs that use this compatibility mode, remember to link against libcompat that is shipped with ECPG. Besides the previously explained syntactic sugar, the Informix compatibility mode ports some functions for input, output and transformation of data as well as embedded SQL statements known from E/SQL to ECPG. Informix compatibility mode is closely connected to the pgtypeslib library of ECPG. pgtypeslib maps SQL data types to data types within the C host program and most of the additional functions of the Informix compatibility mode allow you to operate on those C host program types. Note however that the extent of the compatibility is limited. It does not try to copy Informix behavior; it allows you to do more or less the same operations and gives you functions that have the same name and the same basic behavior but it is no drop-in replacement if you are using Informix at the moment. Moreover, some of the data types are different. For example, PostgreSQL's datetime and interval types do not know about ranges like for example YEAR TO MINUTE so you won't find support in ECPG for that either. 33.15.1. Additional Types The Informix-special "string" pseudo-type for storing right-trimmed character string data is now supported in Informix-mode without using typedef. In fact, in Informix-mode, ECPG refuses to process source files that contain typedef sometype string; EXEC SQL BEGIN DECLARE SECTION; string userid; /* this variable will contain trimmed data */ EXEC SQL END DECLARE SECTION; EXEC SQL FETCH MYCUR INTO :userid; 33.15.2. Additional/Missing Embedded SQ