Re: [Squirrel-sql-users] String to date conversion error despiteglobal prefs

[Doug L. <la...@ni...>]

Hi Jane.

Try setting environment variable DBDATE and/or GL_DATE, which can be =
used in an Informix client process to change the date string format. In =
USA, you would normally use:

    DBDATE=3DMDY4/
    GL_DATE=3D%D

As these are supposed to be the default, I expect one of them is set to =
something else in the Informix engine.

There is also GL_DATETIME for DATETIME representation. The default is =
"%Y-%m-%d %H:%M:%S", for example producing "2006-12-31 10:45" (depending =
on precision).

Alternatively, you can use built-in functions TO_DATE and TO_CHAR in an =
Informix SQL statement to convert between string and DATE or DATETIME =
values using a specified GLS format using the same conventions as =
GL_DATETIME.

See: http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp

Regards,=20
Doug Lawry=20
www.douglawry.webhop.org



----- Original Message -----=20
From: jan...@ya...=20
To: squ...@li...=20
Sent: Sunday, July 01, 2007 10:58 PM
Subject: Re: [Squirrel-sql-users] String to date conversion error =
despiteglobal prefs


Robert,=20
thanks for the response.

I tried to use the "date" function using a MM/DD/YYYY format:

date('02/02/2002')

 but I get the same error.

Now the thing is, if I use a non-java based client, like winsql (which =
is not that great compared to squirrel) for example, all's fine, =
although I have no setting that tells winsql how to treat a date =
literal. So I don't think the problem lies with the database =
configuration itself, but with jdbc, because some Java apps I've written =
in Java also default the date literal to 'yyyy/mm/dd'. Is there a way to =
change this thru squirrel?

Thanks.



Robert Manning <rob...@gm...> wrote:=20
  On 6/22/07, jan...@ya... wrote:
  > Hi,
  >
  > I get a "String to date conversion error" when I'm trying to do =
this:
  >
  > update bla set blabla=3D'6/21/2007'

  It looks like the database is performing an implicit conversion (you
  gave it a string and the column is a date - it needs to reconcile that
  somehow). I'm guessing here as I have little knowledge of Informix.
  Generally, date types can be obtained by using a date function on a
  string that has a date format that the date function can understand.
  I suppose the first format you chose was not what the implicit
  conversion was expecting. You should consult your Informix product
  manual for guidance on what the format of date strings can be and if
  there are date functions that can/should be used.

  The date datatype format choices in SQuirreL apply to how dates are
  displayed and edited. So, any SQL with date strings that you execute
  goes to the database as is.

  Rob