|
I am now back in the office and have checked how I applied this in practice
on my installation. I appended the JDBC URL with (for UK):
;DBDATE=DMY4/;DBCENTURY=C
You might also want to set DBCENTURY as I have done, which means Informix
will convert 2-digit years to whichever is closest to the current date.
I didn't need to set GL_DATE or GL_DATETIME.
----- Original Message -----
Sent: Monday, July 02, 2007 8:07 AM
Subject: Re: [Squirrel-sql-users] String to date conversion error
despiteglobal prefs
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=MDY4/
GL_DATE=%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.
----- Original Message -----
Sent: Sunday, July 01, 2007 10:58 PM
Subject: Re: [Squirrel-sql-users] String to date conversion error
despiteglobal prefs
Robert,
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
wrote:
On
6/22/07, janekindred@xxxxxxxxx wrote:
>
Hi,
>
> I get a "String to date conversion error" when I'm trying
to do this:
>
> update bla set blabla='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
|
-------------------------------------------------------------------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/_______________________________________________
Squirrel-sql-users mailing list
Squirrel-sql-users@xxxxxxxxxxxxxxxxxxxxx
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
| 
