Home » SQL & PL/SQL » SQL & PL/SQL » to_char date conversion not working (oracle 11 for Windows 10)
to_char date conversion not working [message #666375] Wed, 01 November 2017 11:42 Go to next message
gunderj
Messages: 10
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 Go to previous messageGo to next message
John Watson
Messages: 7149
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 #666377 is a reply to message #666376] Wed, 01 November 2017 12:18 Go to previous messageGo to next message
gunderj
Messages: 10
Registered: April 2016
Location: California
Junior Member
Thanks but if the string is a valid date then it should go in, no? How can I guarantee that the current time is carried into the variable. My problem is that it is always midnight.
Thanks
Re: to_char date conversion not working [message #666378 is a reply to message #666377] Wed, 01 November 2017 12:22 Go to previous messageGo to next message
BlackSwan
Messages: 25716
Registered: January 2009
Location: SoCal
Senior Member
>select to_char(sysdate, 'dd-mon-yyyy hh:mi:ss') into d2 from dual;
above is HIGHLY inefficient & can be done faster & without error by doing as below
d2 := sysdate;
Since both SYSDATE & D2 are DATE; there is ZERO requirement to convert to a string (TO_CHAR)
Re: to_char date conversion not working [message #666380 is a reply to message #666378] Wed, 01 November 2017 12:26 Go to previous messageGo to next message
gunderj
Messages: 10
Registered: April 2016
Location: California
Junior Member
Perfect and thanks.
Re: to_char date conversion not working [message #666381 is a reply to message #666380] Wed, 01 November 2017 12:34 Go to previous messageGo to next message
BlackSwan
Messages: 25716
Registered: January 2009
Location: SoCal
Senior Member
>select sysdate into d1 from dual;
why are you abusing SELECT instead of simple assignment?
d1 := sysdate;
Re: to_char date conversion not working [message #666382 is a reply to message #666377] Wed, 01 November 2017 12:51 Go to previous messageGo to next message
John Watson
Messages: 7149
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 #666383 is a reply to message #666377] Wed, 01 November 2017 13:15 Go to previous messageGo to next message
BlackSwan
Messages: 25716
Registered: January 2009
Location: SoCal
Senior Member
gunderj wrote on Wed, 01 November 2017 10:18
Thanks but if the string is a valid date then it should go in, no?

'10-11-12'
Which is correct DATE below for string above?
I'll give you 6 guesses since the first 5 will be wrong.
Oct. 11 2012 'MM-DD-RR'
Nov. 10 2012 'DD-MM-RR'
Nov. 12 2010 'RR-MM-DD'
Dec. 11 2010 'RR-DD-MM'
Oct. 12 2011 'MM-RR-DD'
Dec. 10 2011 'DD-RR-MM'

any or all are valid dates; DEPENDING upon which mask is applied to the string!
Re: to_char date conversion not working [message #666393 is a reply to message #666383] Thu, 02 November 2017 04:15 Go to previous message
cookiemonster
Messages: 12928
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.
Previous Topic: NO_CPU_COSTING hint
Next Topic: ORA-06502: PL/SQL: numeric or value error.
Goto Forum:
  


Current Time: Tue Nov 21 04:22:24 CST 2017

Total time taken to generate the page: 0.01935 seconds