Re: to_char(date) gives the result 00-000-00

On Mar 26, 10:55 am, "Ana C. Dent" <anaced..._at_hotmail.com> wrote:
> "Narendra" <narendra..._at_gmail.com> wrote innews:1174890708.187769.146910_at_y80g2000hsf.googlegroups.com:
>
>
>
>
>
>
>
> > first row was inserted using the sql insert.
> > remaining rows are inserted from the c++ engine
>
> > When i do to_char of the date y it give me 00-000-00
>
> > SQL> select to_char(y),y from x;
>
> > TO_CHAR(Y) Y
> > --------------- ---------
> > 23-MAR-07 23-MAR-07
> > 00-000-00 01-JUN-01
> > 00-000-00 01-JUL-01
> > 00-000-00 01-OCT-01
>
> > But when i do to_char(to_date(y)) it give the correct dates
>
> > SQL> select to_char(to_date(y)),y from x;
>
> > TO_CHAR(TO_DATE Y
> > --------------- ---------
> > 23-MAR-07 23-MAR-07
> > 01-JUN-01 01-JUN-01
> > 01-JUL-01 01-JUL-01
> > 01-OCT-01 01-OCT-01
>
> > SQL> select dump(y) from x;
>
> > DUMP(Y)
> > ----------------------------------------------------------------------
> -
> > --------- Typ=12 Len=7: 120,107,3,23,16,52,56
> > Typ=12 Len=7: 120,101,6,1,0,0,0
> > Typ=12 Len=7: 120,101,7,1,0,0,0
> > Typ=12 Len=7: 120,101,10,1,0,0,0
>
> > Can anyone tell me what the problem is and how can it be resolved?
>
> Problem is PEBKAC.
> Below might work better for you.
> select to_char(y,'DD-MON-YYY'),y from x;- Hide quoted text -
>
> - Show quoted text -

Nardendra, I was going to suggest checking your session settings for the NLS parameters related to the default date format since a to_char(col) on a date column should produce the default session format.

  1* select fld3, to_char(fld3), to_char(fld3,'YYYYMMDD') UT1 > /

FLD3 TO_CHAR(F TO_CHAR(
--------- --------- --------
18-JAN-07 18-JAN-07 20070118 HH -- Mark D Powell -- Received on Mon Mar 26 2007 - 10:41:45 CDT