getting ORA-01858 when using INSERT INTO SELECT ... (a non-numeric character was found where a numeric was expected)

From: Jens Kisters <j.kisters_at_rosomm-partner.de>
Date: Sat, 1 Dec 2001 18:05:12 +0100
Message-ID: <9ub2bh$sob$03$2@news.t-online.com>

Hey there,

I've encountered a problem i couldnt find an answer to in the newsgroup archives.

i have two identical tables.

SQL> desc backup_scores;

 Name                                                  Null?    Typ
 ----------------------------------------------------- -------- ------------


-

 WEEK                                                  NOT NULL DATE
 NICK                                                  NOT NULL VARCHAR2(30)
 GAMEID                                                NOT NULL NUMBER(9)
 GAMEDATE                                              NOT NULL DATE
 SSO_USER_ID                                           NOT NULL NUMBER(12)
 SCORE                                                 NOT NULL NUMBER(14,4)

SQL> desc scores;
 Name                                                  Null?    Typ
 ----------------------------------------------------- -------- ------------


-

 NICK                                                  NOT NULL VARCHAR2(30)
 WEEK                                                  NOT NULL DATE
 GAMEID                                                NOT NULL NUMBER(9)
 GAMEDATE                                              NOT NULL DATE
 SSO_USER_ID                                           NOT NULL NUMBER(12)
 SCORE                                                 NOT NULL NUMBER(14,4)

i can do

SQL> SELECT * FROM TF1b.scores

  2       WHERE week
  3        BETWEEN TO_DATE('20000101000000','YYYYMMDDHH24MISS')
  4        AND TO_DATE('20011129235959','YYYYMMDDHH24MISS')
  5        AND TO_DATE('20000101000000','YYYYMMDDHH24MISS')
  6         < TO_DATE('20011129235959','YYYYMMDDHH24MISS');

and get a result.

NICK                           WEEK        GAMEID GAMEDATE SSO_USER_ID

SCORE
------------------------------ -------- --------- -------- ----------- -----

---

player355                      19.11.01        10 15.11.01   4,670E+09

0

if i try the same in conjunction with a INSERT

INSERT INTO TF1b.backup_scores

     SELECT * FROM TF1b.scores
     WHERE week
      BETWEEN TO_DATE('20000101000000','YYYYMMDDHH24MISS')
      AND TO_DATE('20011129235959','YYYYMMDDHH24MISS')
      AND TO_DATE('20000101000000','YYYYMMDDHH24MISS')
       < TO_DATE('20011129235959','YYYYMMDDHH24MISS')

it fails giving me an error

ORA-01858: a non-numeric character was found where a numeric was expected

but all numbers in the TO_DATE call are where they are supposed to be, the select works fine.
I did the same to backup several oter tables that contain DATE colums. Whats happening here?

thanks a lot in advance
Jens Received on Sat Dec 01 2001 - 11:05:12 CST