Prev: some errors when using impdp
Next: 10g ORA-30625: method dispatch on NULL SELF argument is disallowed
From: Narendra on 26 Mar 2007 02:31
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?

From: Ana C. Dent on 26 Mar 2007 10:55
"Narendra" <narendra.hn(a)gmail.com> wrote in
news:1174890708.187769.146910(a)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;
From: Mark D Powell on 26 Mar 2007 11:41
On Mar 26, 10:55 am, "Ana C. Dent" <anaced...(a)hotmail.com> wrote:
> "Narendra" <narendra...(a)gmail.com> wrote innews:1174890708.187769.146910(a)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 --



 | 
Pages: 1
Prev: some errors when using impdp
Next: 10g ORA-30625: method dispatch on NULL SELF argument is disallowed