This should work as Michael stated, I've used it
frequently from DBI myself.
Try it from sqlplus and see if it works properly there.
If not, you have a non-DBI problem.
Jared
On Monday 27 January 2003 08:52, Doug Hughes wrote:
> 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.
