Try setting it as an environmental variable. It is critical that this get done prior
to initiating the connection. It should be something like:
$ENV{NLS_DATE_FORMAT}='YYYY/MM/DD HH24:MI:SS';
-----Original Message-----
From: Doug Hughes [mailto:[EMAIL PROTECTED]]
Sent: Monday, January 27, 2003 11:52 AM
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.
