Re: I cant understand the DATE values stored in V$SQL_BIND_CAPTURE table (Oracle 10.1.0.4 and up)!

On Jul 4, 1:02 am, "yoav.ora..._at_gmail.com" <yoav.ora..._at_gmail.com> wrote:
> EdStevens :
>
>
>
> > On Jul 3, 7:03 am, yoav.ora..._at_gmail.com wrote:
> > > I am trying to read the values stored in V$SQL_BIND_CAPTURE. For
> > > string and numeric values, everything is OK but when I look into DATE
> > > values I get confused. Here is the query I am running:
>
> > > SELECT VALUE_STRING FROM V$SQL_BIND_CAPTURE WHERE
> > > DATATYPE_STRING='DATE';
>
> > > It seems that no matter what the default date format is or what the
> > > NLS_LANG is,
> > > the values are stored in MM/DD/YY HH24:MI:SS format.
>
> > > I couldn't find any documentation for this. I am looking for 2
> > > answers:
>
> > > 1.In case you have a non-English instance (from SQL*PLUS write "show
> > > parameter nls_lang"
> > > and verify it is not AMERICAN), do you get the same format for
> > > DATEs stroed in
> > > $SQL_BIND_CAPTURE?
> > > 2. Do you know what the Oracle expected behavior is?
>
> > > Any help would be appreciated.
>
> > > Thanks
> > > Yoav
>
> > SQL> desc V$SQL_BIND_CAPTURE
> > Name Null? Type
> > ----------------------------------------- -------- --------------
>
> > <snip>
> > VALUE_STRING VARCHAR2(4000)
> > VALUE_ANYDATA SYS.ANYDATA
>
> > The column VALUE_STRING is not a date, it is a character string.
> > Doesn't matter that the character string it contains may *look* like
> > a date ....
>
> i know that, but when the type of the bind is "date" i need to know
> the date format of the value field if i want to use it

>From your first post:

"It seems that no matter what the default date format is or what the NLS_LANG is,
  the values are stored in MM/DD/YY HH24:MI:SS format. "

What am I missing? You said yourself the format is MM/DD/YY HH24:MI:SS Received on Wed Jul 04 2007 - 12:14:55 CDT