Propably off exact thread or topic, but what if one converts the data of the
table first to string using the format found out to match the data format,
and then back to date. Not efficient but works:
SELECT
...
/*need to calculate date difference */
(TO_DATE(TO_CHAR(required_date), 'YYYY-MM-DD') -
TO_DATE(TO_CHAR(delivered_date, 'YYYY-MM-DD')) "Late",
...
Pekka Siiskonen
> -----Original Message-----
> From: Doug Hughes [mailto:[EMAIL PROTECTED]]
> Sent: 27. tammikuuta 2003 18:52
> To: Michael A Chase
> Cc: [EMAIL PROTECTED]
> Subject: Re: Oracle select of date fields
>
>
> On Sun, 26 Jan 2003, Michael A Chase wrote:
>
> > On Mon, 27 Jan 2003 00:24:58 -0700 (MST) Doug Hughes
> <[EMAIL PROTECTED]> wrote:
> >
> > > I've got some fields in an oracle table where the internal format
> > > is date. I'm currently using a select * type query to return
> > > all the colums of the table (36), and the date fields come back
> > > as 'MM/DD/YYYY'.
> > > I could enumerate every column and for each data field do
> > > a select to_char(field,'MM/DD/YYYY HH24:MI:SS'), but that
> > > seems rather tedious and I was thinking there must be a better
> > > way. Is there some way to do the equivalent of an alter system
> > > to set the NLS_DATE_FORMAT for retrieving date fields?
> >
> > You'll need to check the fine Oracle SQL manual to be sure, but I
> > think "ALTER SESSION SET NLS DATE FORMAT TO 'YYYY/MM/DD HH24:MI:SS'"
> > or something similar would do what you want. I recommend
> putting the
> > year first since that makes sorting and parsing easier.
> >
>
> yup. Tried that. put it in a $dbh->do
>
> $dbsrc->do("ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD
> HH24:MI:SS'")
> || die $dbsrc->errstr;
>
> It executes just fine, but it doesn't effect the date results I get
> in the query.
>
> To see what I mean try something like this:
> $dbsrc->do("ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD
> HH24:MI:SS'") ||
> die $dbsrc->errstr;
>
> print "$_ " for $dbsrc->selectrow_array("select sysdate from
> dual") or die
> "Couldn't get date $DBI->errstr";
>
> it still shows as 24-JAN-03 for me.
>
>
