IDS Forum

RE: Date Format

Posted By: Chris Salch
Date: Thursday, 4 January 2007, at 3:23 p.m.

In Response To: RE: Date Format (Chris Salch)

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

Messages In This Thread

• Date Format
  CHRIS SALCH -- Thursday, 4 January 2007, at 12:29 p.m.
  • RE: Date Format
    Robert Roussey\(MIS\) -- Thursday, 4 January 2007, at 2:12 p.m.
    • RE: Date Format
      Chris Salch -- Thursday, 4 January 2007, at 2:29 p.m.
      • RE: Date Format
        Chris Salch -- Thursday, 4 January 2007, at 3:23 p.m.
        • Re: Date Format
          Jonathan Leffler -- Friday, 5 January 2007, at 3:15 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.