Date Format

Informix 9.40.HC5 on HPUX 11iV1 This question probably has a very simple answer to it that I have been unable to find. Is there a way to set the date format used to interpret string literal dates on a per connection basis? We have several legacy applications that use month day year and some new applications, developed in java, that are defaulting to year month day. It wouldn't be an issue except we need to have both applications call the same stored procedures and views. The date format used seems to be propagating to anything that reads a date from a string literal in the context of a connection. So, using one date order in a stored procedure or view will work when called from java but not when called from the legacy app. There is no way I can hunt down all the literal dates and escape them with DATETIME. Any ideas?

The Setnet utility for setting up the ODBC on the Windoze client has many environmental vars that can be set, including DBDATE. Perhaps you could create 2 different DSNs, one for the Java apps, one for the traditional apps, setting the DBDATE differently for each one? Bob Roussey Unix / Informix Administration Spirit Airlines Robert.Roussey@SpiritAir.com -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of CHRIS SALCH Sent: Thursday, January 04, 2007 12:30 PM To: ids@iiug.org Subject: Date Format [8114] Informix 9.40.HC5 on HPUX 11iV1 This question probably has a very simple answer to it that I have been unable to find. Is there a way to set the date format used to interpret string literal dates on a per connection basis? We have several legacy applications that use month day year and some new applications, developed in java, that are defaulting to year month day. It wouldn't be an issue except we need to have both applications call the same stored procedures and views. The date format used seems to be propagating to anything that reads a date from a string literal in the context of a connection. So, using one date order in a stored procedure or view will work when called from java but not when called from the legacy app. There is no way I can hunt down all the literal dates and escape them with DATETIME. Any ideas? ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.

No go there. The apps I'm working with have components on the HPUX server, connection through ODBC on windows, connecting through a pure JDBC driver in java. I can't rely on environment variables at the client end to do what I want. Part of the issue has to do with a perl script that runs server side, launched from stored procedure. (I know this is nasty but it's the only technique I've found to reference this library call off the server) The perl script calls a routine, written in c, through some esoteric nightmare our vendor built. When you call that stored procedure from a isql, dbaccess, or a windows odbc connection it works perfectly. Calling it from java via the pure java driver cause it to use a different date format all the way down the line. There has to be something I can set, either via sql or on the java driver that controls the literal date format. I can't see any other way that this could happen (To tell you the truth, I can't see how it's happening now . . .) On Thu, 2007-01-04 at 14:12 -0500, Robert Roussey(MIS) wrote: > The Setnet utility for setting up the ODBC on the Windoze client has > many environmental vars that can be set, including DBDATE. Perhaps you > could create 2 different DSNs, one for the Java apps, one for the > traditional apps, setting the DBDATE differently for each one? > > Bob Roussey > Unix / Informix Administration > Spirit Airlines > Robert.Roussey@SpiritAir.com > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > CHRIS SALCH > Sent: Thursday, January 04, 2007 12:30 PM > To: ids@iiug.org > Subject: Date Format [8114] > > Informix 9.40.HC5 on HPUX 11iV1 > > This question probably has a very simple answer to it that I have been > unable > to find. Is there a way to set the date format used to interpret string > literal dates on a per connection basis? > > We have several legacy applications that use month day year and some new > > applications, developed in java, that are defaulting to year month day. > It > wouldn't be an issue except we need to have both applications call the > same > stored procedures and views. The date format used seems to be > propagating to > anything that reads a date from a string literal in the context of a > connection. So, using one date order in a stored procedure or view will > work > when called from java but not when called from the legacy app. There is > no way > I can hunt down all the literal dates and escape them with DATETIME. Any > > ideas? > > ************************************************************************ > ******* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. -- ----------------------- Chris Salch Programmer Analyst LeTourneau University 903-233-3537

It is messy. Unfortunately, your suggestion won't do me much good either. I'm not having trouble with returned dates but dates that have been coded in where clauses. Depending on where (ODBC and ISQL or JDBC) the engine interprets those dates differently. Or at least that's what I'm seeing. For example ( my syntax may be off here.) create table stored_dates ( date1 date ); create view view_dates as select * from stored_dates where date1 >'1-2-2006' ; SELECT * FROM view_dates ; If I run the select statement from isql or our odbc based app it works. If I run the select statement from the jdbc based app it bombs with a string to date conversion error or such. create table stored_dates ( date1 date ); create view view_dates as select * from stored_dates where date1 >'2006-2-1' ; SELECT * FROM view_dates ; If I run the select from the jdbc app now, it works. On the other hand if I run the select from isql or our odbc based app it dies with an error about the date. On Thu, 2007-01-04 at 15:21 -0500, Gentry, Mark wrote: > How about modifying your stored procedure(s) to test of the type of "tty" > the use is and return strings accordingly. Network users will not have the > usual device driver "tty" but rather some network assigned "tty" like RAL228 > where "RAL" is associated with a particular network server. Sounds messy > and a potential resource hog. > > -----Original Message----- > From: Chris Salch [mailto:chrissalch@letu.edu] > Sent: Thursday, January 04, 2007 2:29 PM > To: ids@iiug.org > Subject: RE: Date Format [8118] > > > No go there. The apps I'm working with have components on the HPUX > server, connection through ODBC on windows, connecting through a pure > JDBC driver in java. I can't rely on environment variables at the > client end to do what I want. > Part of the issue has to do with a perl script that runs server side, > launched from stored procedure. (I know this is nasty but it's the only > technique I've found to reference this library call off the server) The > perl script calls a routine, written in c, through some esoteric > nightmare our vendor built. When you call that stored procedure from a > isql, dbaccess, or a windows odbc connection it works perfectly. > Calling it from java via the pure java driver cause it to use a > different date format all the way down the line. There has to be > something I can set, either via sql or on the java driver that controls > the literal date format. I can't see any other way that this could > happen (To tell you the truth, I can't see how it's happening now . . .) > > On Thu, 2007-01-04 at 14:12 -0500, Robert Roussey(MIS) wrote: > > The Setnet utility for setting up the ODBC on the Windoze client has > > many environmental vars that can be set, including DBDATE. Perhaps you > > could create 2 different DSNs, one for the Java apps, one for the > > traditional apps, setting the DBDATE differently for each one? > > > > Bob Roussey > > Unix / Informix Administration > > Spirit Airlines > > Robert.Roussey@SpiritAir.com > > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > CHRIS SALCH > > Sent: Thursday, January 04, 2007 12:30 PM > > To: ids@iiug.org > > Subject: Date Format [8114] > > > > Informix 9.40.HC5 on HPUX 11iV1 > > > > This question probably has a very simple answer to it that I have been > > unable > > to find. Is there a way to set the date format used to interpret string > > literal dates on a per connection basis? > > > > We have several legacy applications that use month day year and some new > > > > applications, developed in java, that are defaulting to year month day. > > It > > wouldn't be an issue except we need to have both applications call the > > same > > stored procedures and views. The date format used seems to be > > propagating to > > anything that reads a date from a string literal in the context of a > > connection. So, using one date order in a stored procedure or view will > > work > > when called from java but not when called from the legacy app. There is > > no way > > I can hunt down all the literal dates and escape them with DATETIME. Any > > > > ideas? > > > > ************************************************************************ > > ******* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > **************************************************************************** > *** > > Forum Note: Use "Reply" to post a response in the discussion forum. -- ----------------------- Chris Salch Programmer Analyst LeTourneau University 903-233-3537

Grump about top-posting! I've trimmed some stuff - probably not enough. On 1/4/07, Chris Salch <chrissalch@letu.edu> wrote: > > It is messy. Unfortunately, your suggestion won't do me much good > either. I'm not having trouble with returned dates but dates that have > been coded in where clauses. Depending on where (ODBC and ISQL or JDBC) > the engine interprets those dates differently. Or at least that's what > I'm seeing. > > For example ( my syntax may be off here.) > > create table stored_dates ( date1 date );> > create view view_dates as > select * > from stored_dates > where date1 >'1-2-2006' ;> > SELECT * > FROM view_dates ;> > If I run the select statement from isql or our odbc based app it works. > If I run the select statement from the jdbc based app it bombs with a > string to date conversion error or such. > > create table stored_dates ( date1 date );> > create view view_dates as > select * > from stored_dates > where date1 >'2006-2-1';> > SELECT * > FROM view_dates ;> > If I run the select from the jdbc app now, it works. On the other hand > if I run the select from isql or our odbc based app it dies with an > error about the date. > > On Thu, 2007-01-04 at 15:21 -0500, Gentry, Mark wrote: > > How about modifying your stored procedure(s) to test of the type of "tty" [...] > > > > -----Original Message----- > > From: Chris Salch [mailto:chrissalch@letu.edu] > > > > No go there. The apps I'm working with have components on the HPUX > > server, connection through ODBC on windows, connecting through a pure > > JDBC driver in java. I can't rely on environment variables at the > > client end to do what I want. > > Part of the issue has to do with a perl script that runs server side, > > launched from stored procedure. (I know this is nasty but it's the only > > technique I've found to reference this library call off the server) The > > perl script calls a routine, written in c, through some esoteric > > nightmare our vendor built. When you call that stored procedure from a > > isql, dbaccess, or a windows odbc connection it works perfectly. > > Calling it from java via the pure java driver cause it to use a > > different date format all the way down the line. There has to be > > something I can set, either via sql or on the java driver that controls > > the literal date format. I can't see any other way that this could > > happen (To tell you the truth, I can't see how it's happening now . . .) > > > > On Thu, 2007-01-04 at 14:12 -0500, Robert Roussey(MIS) wrote: > > > The Setnet utility for setting up the ODBC on the Windoze client has > > > many environmental vars that can be set, including DBDATE. Perhaps you > > > could create 2 different DSNs, one for the Java apps, one for the > > > traditional apps, setting the DBDATE differently for each one? > > > > > > -----Original Message----- > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > > CHRIS SALCH > > > > > > Informix 9.40.HC5 on HPUX 11iV1 > > > > > > This question probably has a very simple answer to it that I have been > > > unable > > > to find. Is there a way to set the date format used to interpret string > > > literal dates on a per connection basis? > > > > > > We have several legacy applications that use month day year and some new > > > applications, developed in java, that are defaulting to year month day. > > > It > > > wouldn't be an issue except we need to have both applications call the > > > same > > > stored procedures and views. The date format used seems to be > > > propagating to > > > anything that reads a date from a string literal in the context of a > > > connection. So, using one date order in a stored procedure or view will > > > work > > > when called from java but not when called from the legacy app. There is > > > no way > > > I can hunt down all the literal dates and escape them with DATETIME. Any > > > ideas? Any use of date notation that is culturally sensitive is sensitive to changes in culture, whether accidental or deliberate. Nice tautologous statement... Basically, using a character string for a DATE is error prone, as you've discovered. There are a variety of tricks you can try. The most dangerous is to start IDS with DBDATE="Y4MD-"; this becomes the default format for interpreting dates unless some application overrides it by setting DBDATE explicitly. Don't do this unless you know what it entails - stuff will break on you. The most nearly sane is to use the only known notation that is not culturally sensitive, which also works in INSERT statements, including a VALUES list. ... WHERE date1 > MDY(2, 1, 2006) You can use the convertibility between DATE and DATETIME YEAR TO DAY to write: WHERE date1 > DATETIME(2006-02-01) YEAR TO DAY There are probably others too - perhaps using the TO_DATE function. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/