to_char date conversion not working [message #666375] |
Wed, 01 November 2017 11:42 |
|
gunderj
Messages: 17 Registered: April 2016 Location: California
|
Junior Member |
|
|
Here is a simple script to re-create a date conversion problem I cannot solve.
drop table t
/
create table t(seq number, d date)
/
declare
d1 date;
d2 date;
begin
select sysdate into d1 from dual;
select to_char(sysdate, 'dd-mon-yyyy hh:mi:ss') into d2 from dual;
insert into t values (1,d1);
insert into t values (2,d2);
end;
/
select seq,to_char(d, 'dd-mon-yy hh:mi:ss'),d from t order by seq
/
At runtime I get ORA-01830 caused by the to-char into d2. The to_char works fine for sysdate from dual but when selecting into a work variable (d2), I get this error...
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string
ORA-06512: at line 7
Thanks if you can advise.
|
|
|
Re: to_char date conversion not working [message #666376 is a reply to message #666375] |
Wed, 01 November 2017 12:14 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
You have type mismatch, which is a straightforward bug: D2 is a date, but you are trying to put a string into it. So Oracle is forced to attempt to do some type casting, relying on defaults for NLS settings. Whether it works is largely a matter of luck. This is what I get:orclx>
orclx> declare
2 d1 date;
3 d2 date;
4
5 begin
6 select sysdate into d1 from dual;
7 select to_char(sysdate, 'dd-mon-yyyy hh:mi:ss') into d2 from dual;
8 insert into t values (1,d1);
9 insert into t values (2,d2);
10 end;
11 /
declare
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at line 7
orclx>
|
|
|
|
|
|
|
Re: to_char date conversion not working [message #666382 is a reply to message #666377] |
Wed, 01 November 2017 12:51 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Quote:Thanks but if the string is a valid date then it should go in, no? Exactly: no. You can't put a string into a date. It has to be cast as a date first. Which, since you are not doing this explicitly, is seriously unreliable. Another example:orclx>
orclx> select sysdate,to_date('1-nov-2017') from dual;
SYSDATE TO_DATE('1-NOV-2017
------------------- -------------------
2017-11-01:17:50:51 0001-11-20:17:00:00
orclx>
|
|
|
|
Re: to_char date conversion not working [message #666393 is a reply to message #666383] |
Thu, 02 November 2017 04:15 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
The only way a date set to sysdate ends up as midnight is if you run the assignment at exactly midnight.
Sysdate always has a time and if you assign it to a date variable or column directly then that variable/column will always get that time.
However if your nls_date_format doesn't include time then you won't see the time when you select the date, even though it's actually there.
|
|
|