Invalid Month in Date - Date format in Informix reverts sporadically

Question

So we have an AIX (ugh) server that runs an ERP system. This system's built in reports suck so I am of course tasked with building the reports we need.

I've got many reports I run and most of course are for specific dates, etc. The coding is done in C# running in ASP.net on a Windows 2003 Standard Server box. It uses the Informix CSDK to connect via the .Net Data Adapter that comes with the CSDK. The server runs Informix 10 on the AIX 5.2 server.

What's weird is that anytime we start and stop Informix or reboot the server, etc, it seems that Informix decides to change the way it's handled the date via the CSDK. If it's currently expecting MM/DD/YYYY then it'll eventually decide after the above situation that is wants it in YYYY/MM/DD. This usually ends up giving me an "Invalid Month in Date" error. Then I go into my dateformat function (made to easily allow me to fall back and forth) and manually change it over. A couple reports I built in the handling of this error and then retry the same query with the other format of the date. This of course is less than ideal and I'd like to get to the bottom of it!

Here is some pasted text from the ASP.net page error. Thanks!

Server Error in '/' Application.
ERROR [HY000] [Informix .NET provider][Informix]Invalid month in date
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about theerror and where it originated in the code.

Exception Details: IBM.Data.Informix.IfxException: ERROR [HY000] [Informix .NET provider][Informix]Invalid month in date

Source Error:

Line 479:
Line 480:        //aUsage = new IfxDataAdapter(sSelect_Usage, conn);
Line 481:        aUsage.Fill(dsUsage, "Usage");
Line 482:        aUsage.Dispose();
Line 483:        dtUsage = dsUsage.Tables["Usage"];


Source File: D:\Inetpub\reports2.oscarwinski.com\App_Code\IMRShipClass.cs    Line: 481

Stack Trace:

[IfxException: ERROR [HY000] [Informix .NET provider][Informix]Invalid month in date]
   IBM.Data.Informix.IfxConnection.HandleError(IntPtr hHandle, SQL_HANDLE hType, RETCODE retcode) +26
   IBM.Data.Informix.IfxCommand.ExecuteReaderObject(CommandBehavior behavior, String method) +654
   IBM.Data.Informix.IfxCommand.ExecuteReader(CommandBehavior behavior) +117
   IBM.Data.Informix.IfxCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +4
   System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +130
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +287
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +92
   IMRShipClass.Generate() in D:\Inetpub\reports2.oscarwinski.com\App_Code\IMRShipClass.cs:481
   IMRShip.testIMR() in D:\Inetpub\reports2.oscarwinski.com\IMRShip.aspx.cs:114
   IMRShip.btnExport2Excel_Click1(Object sender, EventArgs e) in D:\Inetpub\reports2.oscarwinski.com\IMRShip.aspx.cs:259
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +111
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +110
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565


Version Information: Microsoft .NET Framework Version:2.0.50727.3082; ASP.NET Version:2.0.50727.3082 

Answer 1

The date format is controlled by the client, not the server. Obviously it has a default (US format), but each client can dictate the format it wishes to use. This is done by setting the DBDATE environment variable. (There is also GL_DATE if you're using locales.)

If the date format has switched, you should check the value of this environment variable. Is it being set by your middleware in some circumstances?

Answer 2

Urgh! .NET is not my strong point - you may have to adapt what I say to work correctly.

On Windows, for better or worse, there is an Informix utility called SETNET32 which can be used to set Informix-related environment variables for Windows programs that connect to Informix databases. You don't mention whether you've set anything using this. Investigate whether it used by the Informix .NET provider.

You also do not mention whether the IDS server instance on the AIX box is restarted when the problem appears. I assume not. (The analysis might be different if it did.)

The main environment variable that controls the date format is DBDATE. It takes various notations, such as:

DBDATE=dmy4/        # 30/07/2009
DBDATE=mdy4/        # 07/30/2009
DBDATE=y4md-        # 2009-07-30
DBDATE=mdy20        # 073009

Don't use the last one. If there are changes in the value of DBDATE, this could account some of your problems, and setting DBDATE would probably fix your problems.

There are other variables that affect date interpretation if DBDATE is not set; these include CLIENT_LOCALE and DB_LOCALE, and even GL_DATE. However, DBDATE has the highest priority and is the one most people set most usually.

Answer 3

set DBDATE=MDY4/ in the client app that runs the reports and every other client as well.