Using SQL Developer 4, I am getting an ORA-01861 exception when I do the following (and BTW, I ticked 'Ignore NLS Definitions'):
alter session set nls_date_format = 'DD-MON-YY';
alter session set nls_timestamp_format = 'DD-MON-YY HH24:MI:SS';
select value from lpp_n3_qas.mes_piece_char pc
join lpp_n3_qas.mes_char_def cd on cd.mes_char_def_id = pc.mes_char_def_id
where
cd.char_name = 'DispatchTimestamp' and
value is not null and
to_timestamp(value, 'DD/MM/YYYY HH24:MI:SS') >= '09-OCT-13' and
to_timestamp(value, 'DD/MM/YYYY HH24:MI:SS') < '16-OCT-13';
----
01862. 00000 - "the numeric value does not match the length of the format item"
*Cause: When the FX and FM format codes are specified for an input date,
then the number of digits must be exactly the number specified by the
format code. For example, 9 will not match the format specifier DD but
09 will.
*Action: Correct the input date or turn off the FX or FM format specifier
in the format string.
But when I do this:
select to_timestamp(value, 'DD/MM/YYYY HH24:MI:SS') as Dispatch_Timestamp from lpp_n3_qas.mes_piece_char pc
join lpp_n3_qas.mes_char_def cd on cd.mes_char_def_id = pc.mes_char_def_id
where
cd.char_name = 'DispatchTimestamp' and
value is not null;
DISPATCH_TIMESTAMP
------------------
09-OCT-13 11:20:06
09-OCT-13 11:20:06
11-OCT-13 16:33:13
11-OCT-13 16:44:25
15-OCT-13 11:23:54
I get no exceptions at all and I used the exact same to_timestamp() conversion.
In addition, if I take one record from the dataset and replace 'value' with it in the first query's where clause I also get no exceptions. The problem doesn't seem to be a bad input timestamp and I don't have any idea about what is going on. Can someone please shed some light?
Aucun commentaire:
Enregistrer un commentaire