Informix Error Invalid Year In Date
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 440k62511824 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 func
(Русский)ישראל (עברית)المملكة العربية السعودية (العربية)ไทย (ไทย)대한민국 (한국어)中华人民共和国 (中文)台灣 (中文)日本 (日本語) HomeLibraryLearnDownloadsTroubleshootingCommunityForums Ask a question Quick access Forums home Browse forums users FAQ Search related threads Remove From My Forums Answered by: Informix error in datareader source SQL Server > SQL Server Integration Services Question 0 Sign in to vote Hi there, I'm using SSIS and I'm entering a sql command in the expression builder of the data flow where http://stackoverflow.com/questions/1560915/im-getting-invalid-month-in-date-trying-to-run-this I have a datareader source. I want to get the data for the datesof the week. I already got it from a table and put it in a string variable. So I have the dateslike this in the variable name "@[User::date]" '5-15-2012', '5-16-2012', '5-17-2012', https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ace53101-11b5-4c33-adbe-dfe9b995b876/informix-error-in-datareader-source?forum=sqlintegrationservices '5-18-2012', '5-19-2012', '5-20-2012', '5-21-2012' I havethis variable in my SSIS package @[User::date] HERE's the query for the datareader source: SELECT sum (a.1) aa, sum (a.2) bb, sum (a.3) cc, a.4, a.date data FROM table WHERE date in (+@[User::date] +) and a.4 = 3 Group By date, a.4 but the package fails at the data reader source step and I get this error : [DataReader Source [1325]] Error: System.Data.Odbc.OdbcException: ERROR [HY000] [Informix][Informix ODBC Driver][Informix]Invalid year in date at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader) at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PreExecute() at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper90 wrapper) Can youhelp me please? Thanks, Sunday, May 27, 2012 2:27 PM Reply | Quote Answers 0 Sign in t
Analytics Conference: Oct 18, Mastering SAP BI, Melbourne: Oct 23, FLBOUG, Clearwater: Oct 28, ASUG Northern California: Nov 2, ASUG Arizona Chapter: Nov 3, ASUG All Texas Chapter Meeting: Nov 11. http://www.forumtopics.com/busobj/viewtopic.php?t=65637&sid=a6463504cfe2ece7fecf175408312dd0 "Invalid month in date" while updating in Informix Search this topic...|Search DI: Designer and Job Design...|Search Box Select a search Explain These Choices... --------------------Recent Topics (All Forums) Unanswered Posts https://bugs.php.net/bug.php?id=57618 Register or Login to Post Forum Index -> Data Integrator -> DI: Designer and Job Design Author Message Hans SegersForum MemberJoined: 21 Jun 2006Posts: 4 Posted: Wed Jun 21, 2006 10:30 amPost informix error subject: "Invalid month in date" while updating in Informix A dataflow contains a Table_Comparison transform to update in an Informix target table. The update fails because of : Invalid month in date. SQL submitted to ODBC data source
UTC Modified: 2007-06-01 18:18 UTC From: edv at leha dot at Assigned: Status: Not a bug Package: PDO_INFORMIX (PECL) PHP Version: 5.2.1 OS: ZEND Core 2.0 Private report: No CVE-ID: View Add Comment Developer Edit [2007-04-10 05:32 UTC] edv at leha dot at Description: ------------ We use ZendCore 2.0.0 with bundled apache2 pdo 1.0.3 (installed from PECL) pdo_informix 1.1.0 (installed from PECL) IBM Informix CSDK Version 2.90 UC1 on RHEL_FC4 32bit DBDATE is exported to Environment with the Apache Startup-Script (/usr/local/Zend/apache2/bin/envvars). Bug 1: ------ setting and exporting the environment var DBDATE="DMY4." in /usr/local/Zend/apache2/bin/envvars only works for insert,update statements - but not for selecting. It also works on Select with a character conversion date::char(10). Within selects the Date-Values are generally shown as YYYY-MM-DD. Bug 2: ------ Direct update/insert using exec with Date-Format as supplied by previous select doesn't work. Supplying the DMY4. format is working. Prepared update/insert using prepare/execute with Date-Format as supplied by previous works - but supplying the DMY4. format is not working. See example below.. Reproduce code: --------------- $pObj=new PDO("informix:host=devdb.leha.at;service=1525;database=stores_demo;server=devdb_net;"); echo "
Environment (DBDATE):".getenv('DBDATE')."
"; $pStmt=$pObj->prepare("select SHIP_DATE::char(10) as SHIP_DATE_As_Char,* from orders where customer_num=?"); $pStmt->execute(array(104)); $aAll=$pStmt->fetch(PDO::FETCH_ASSOC); print_r($aAll); /* # Test for same result with direct query $pStmt=$pObj->query("select * from orders where customer_num=104"); $aAll=$pStmt->fetch(PDO::FETCH_ASSOC); print_r($aAll); */ $cDate="18.12.2006"; $nRows=$pObj->exec("update orders set SHIP_DATE='$cDate' where ORDER_NUM=1001"); printf ("
Executing direct statement 1 with %s (Rows: %0d)
",$cDate,$nRows); print_r($p