Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01830 error.
ORA-01830 error. [message #8218] Tue, 05 August 2003 00:06 Go to next message
Meenakshi
Messages: 28
Registered: December 2001
Junior Member
declare
time_in_zone date;
begin
select to_char(SYSDATE, 'DD-MON-YYYY HH:MM:SS') into time_in_zone from dual;
END;
/

declare
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string
ORA-06512: at line 4

Can someone help me clear this up?
Thanks.

Report message to a moderator

Re: ORA-01830 error. [message #8220 is a reply to message #8218] Tue, 05 August 2003 00:21 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
2 remarks:
First of all, your date format string isn't very logical:

MM = numeric month representation
MI = minutes
so your date format string should be:
DD-MON-YYYY HH:MI:SS

But even more important:
you define a DATE variable time_in_zone, yet you intent to use it as a VARCHAR2 variable. You should either loose the TO_CHAR (which converts a DATE to a VARCHAR2) or change the data type of your variable to VARCHAR2:
SQL> declare
  2    v_date date;
  3  begin
  4    <B>SELECT SYSDATE</B>
  5     <B>INTO v_date</B>
  6     FROM dual;
  7  end;
  8  /

PL/SQL procedure successfully completed.
SQL> DECLARE
  2    v_date <B>VARCHAR2(20)</B>;
  3  BEGIN
  4    SELECT to_char(SYSDATE
  5                  ,'DD-MON-YYYY HH:MI:SS'
  6                  )
  7     INTO v_date
  8     FROM dual;
  9  END;
 10  /

PL/SQL procedure successfully completed.

SQL>
MHE

Report message to a moderator

Re: ORA-01830 error. [message #8221 is a reply to message #8218] Tue, 05 August 2003 00:23 Go to previous messageGo to next message
Meenakshi
Messages: 28
Registered: December 2001
Junior Member
Actually, this is the entire script I am trying to run.

declare
time_in_zone date;
new_york_time date;
begin
select to_char(SYSDATE, 'DD-MON-YYYY HH:MM:SS') into time_in_zone from dual;
new_york_time := new_time(time_in_zone,'PST', 'EST');
dbms_output.put_line(new_york_time);
END;
/

I get this error:

Input truncated to 1 characters
declare
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string
ORA-06512: at line 5

Thanks.

Report message to a moderator

Re: ORA-01830 error. [message #8222 is a reply to message #8220] Tue, 05 August 2003 00:32 Go to previous messageGo to next message
Meenakshi
Messages: 28
Registered: December 2001
Junior Member
Hi,

Thank you for your response. My problem is, if I use the to_char function, I am unable to use the new_time function on the output. But if I use the to_date function, it doesn't display the HH:MI:SS portion of the date. Where is this failing? Can you please help?

Thanks again.

Report message to a moderator

Re: ORA-01830 error. [message #8223 is a reply to message #8222] Tue, 05 August 2003 02:27 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
You have to use the to_char in the DBMS_OUTPUT.PUT_LINE. It is there you want to show the time portion:
SQL> declare
  2  time_in_zone date;
  3  new_york_time date;
  4  begin
  5 new_york_time := new_time(time_in_zone,'PST', 'EST');
  6  dbms_output.put_line(to_char(new_york_time,'DD-MON-YYYY HH24:MI:SS'));
  7  END;
  8  /
05-AUG-2003 12:43:59

PL/SQL procedure successfully completed.

SQL>
But let's take a closer look at your function, because it does a lot of things you don't need. In the end, even the procedure itself isn't needed.

Look at the evolution in the procedure, I've done it step by step so you see what obsolete steps you've done:
SQL> ed
Wrote file afiedt.buf

  1  declare
  2  time_in_zone date := SYSDATE; -- assign SYSDATE directly, no fetch needed.
  3  new_york_time date;
  4  begin
  5  --select sysdate into time_in_zone from dual; -- put fetch in comment, no longer necessary
  6  new_york_time := new_time(time_in_zone,'PST', 'EST');
  7  dbms_output.put_line(to_char(new_york_time,'DD-MON-YYYY HH24:MI:SS'));
  8* END;
SQL> /
05-AUG-2003 12:45:38

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

  1  declare
  2  --time_in_zone date := SYSDATE; -- this date is obsolete, so we put it in comment
  3  new_york_time date;
  4  begin
  5  new_york_time := new_time(SYSDATE,'PST', 'EST'); -- we use SYSDATE directly instead of assigning it to a variable and using that variable.
  6  dbms_output.put_line(to_char(new_york_time,'DD-MON-YYYY HH24:MI:SS'));
  7* END;
SQL> /
05-AUG-2003 12:45:53

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

  1  declare
  2  --new_york_time date; -- we don't need this date either, we can use the new_time function in the dbms_output.put_line
  3  begin
  4  --new_york_time := new_time(SYSDATE,'PST', 'EST');
  5  dbms_output.put_line(to_char(new_time(SYSDATE,'PST', 'EST'),'DD-MON-YYYY HH24:MI:SS'));
  6* END;
SQL> /
05-AUG-2003 12:46:16

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

  1  begin
  2  dbms_output.put_line(to_char(new_time(SYSDATE,'PST', 'EST'),'DD-MON-YYYY HH24:MI:SS')); -- This is all that's needed for the procedure
  3* END;
SQL> /
05-AUG-2003 12:46:25

PL/SQL procedure successfully completed.

SQL> exec dbms_output.put_line(to_char(new_time(SYSDATE,'PST', 'EST'),'DD-MON-YYYY HH24:MI:SS')); -- we can even use EXECUTE instead of BEGIN..code..END
05-AUG-2003 12:48:23

PL/SQL procedure successfully completed.
SQL> select to_char(new_time(SYSDATE,'PST', 'EST'),'DD-MON-YYYY HH24:MI:SS') new_york_time 
  2    from dual;

NEW_YORK_TIME
--------------------
05-AUG-2003 12:48:46

As you can see in the last sample, We don't even need PL/SQL, we can do it in SQL too.
Now, as for the time related question. Oracle has a default date format it uses to displays dates: NLS_DATE_FORMAT. In most cases, it is set to 'DD-MON-YY'.

You can find it in your registry under HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE/.
If it isn't present you can create it yourself and give NLS_DATE_FORMAT any date format accepted by Oracle. Or, if you don't want to play around with the registry you can alter the date format just for the session:
SQL> select new_time(SYSDATE,'PST', 'EST') new_york_time
  2    from dual;

NEW_YORK_
---------
05-AUG-03

SQL> alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';

Session altered.

SQL> select new_time(SYSDATE,'PST', 'EST') new_york_time
  2    from dual;

NEW_YORK_TIME
-------------------
05-08-2003 13:03:37

SQL> alter session set nls_date_format='DD-MM-YYYY HH AM:MI:SS';

Session altered.

SQL> select new_time(SYSDATE,'PST', 'EST') new_york_time
  2    from dual;

NEW_YORK_TIME
----------------------
05-08-2003 01 PM:04:42

SQL>


HTH,
MHE

Report message to a moderator

Thanks a lot!!!!!!!!!!! [message #8225 is a reply to message #8223] Tue, 05 August 2003 02:45 Go to previous messageGo to next message
Meenakshi
Messages: 28
Registered: December 2001
Junior Member
I'm brand new at this, and your explanation was very helpful. Thanks again, my code looks streamlined and very functional now.

Rgds,

Report message to a moderator

Re: ORA-01830 error. [message #8246 is a reply to message #8221] Tue, 05 August 2003 16:49 Go to previous messageGo to next message
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
Senior Member
TIME_IN_ZONE is a date.
SYSDATE is a date.
What is TO_CHAR doing? Why SELECT FROM DUAL?

Report message to a moderator

Re: ORA-01830 error. [message #10707 is a reply to message #8222] Wed, 11 February 2004 05:28 Go to previous messageGo to next message
kris
Messages: 43
Registered: February 2002
Member
Hi Folks,
Was going thru this thread with simillar kind of problem. Could you help me with this?
Anticipating an early reply from you.
Thanks
Kris Kumsi
Insert into CONTRACT_T (CONTRACT_ID,BEGIN_DATE,END_DATE,MARKET_SERVICE,SERVICE_TYPE,CONTRACT_TYPE,LOCATION_ID,BUYER_ID,SELLER_ID,INTERNAL_REFERENCE_ID,TERMINATION_DATE,CONFIRMATION_LEVEL,FIXED_AMOUNT_FLAG,CONFIRMED,PENDING_REQUEST_BY,PENDING_PROFILE,SUBMIT_DATE,LAST_UPDATED_DATE,FIXED_AMOUNT_PATTERN) Values (680,'01/01/2002 4:00:00 AM','02/01/2002 4:00:00 AM',21,'NA','EBM',506,8,50010,'EXTREF680','','C','N','Y','','N','12/30/2001 4:00:00 AM','01/01/2002 3:00:00 AM','')
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

Report message to a moderator

Re: ORA-01830 error. [message #10722 is a reply to message #10707] Thu, 12 February 2004 03:52 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
Senior Member
Assuming that the columns that you are trying to insert the dates and times into are of date datatype as they should be, you should always use the to_date function and specify the format to do explicit conversions from varchar2 to date, so that it doesn't matter whether your nls_date_format matches or not. Please see the corrected code below.

Insert into CONTRACT_T 
  (CONTRACT_ID,
   BEGIN_DATE,
   END_DATE,
   MARKET_SERVICE,
   SERVICE_TYPE,
   CONTRACT_TYPE,
   LOCATION_ID,
   BUYER_ID,
   SELLER_ID,
   INTERNAL_REFERENCE_ID,
   TERMINATION_DATE,
   CONFIRMATION_LEVEL,
   FIXED_AMOUNT_FLAG,
   CONFIRMED,
   PENDING_REQUEST_BY,
   PENDING_PROFILE,
   SUBMIT_DATE,
   LAST_UPDATED_DATE,
   FIXED_AMOUNT_PATTERN) 
Values 
  (680,
   <b>TO_DATE ('01/01/2002 4:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')</b>,
   '02/01/2002 4:00:00 AM',
   21,
   'NA',
   'EBM',
   506,
   8,
   50010,
   'EXTREF680',
   '',
   'C',
   'N',
   'Y',
   '',
   'N',
   <b>TO_DATE ('12/30/2001 4:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')</b>,
   <b>TO_DATE ('01/01/2002 3:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')</b>,
   '');

Report message to a moderator

Re: ORA-01830 error. [message #10723 is a reply to message #10707] Thu, 12 February 2004 03:54 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
Senior Member
Assuming that the columns that you are trying to insert the dates and times into are of date datatype as they should be, you should always use the to_date function and specify the format to do explicit conversions from varchar2 to date, so that it doesn't matter whether your nls_date_format matches or not. Please see the corrected code below.

Insert into CONTRACT_T 
  (CONTRACT_ID,
   BEGIN_DATE,
   END_DATE,
   MARKET_SERVICE,
   SERVICE_TYPE,
   CONTRACT_TYPE,
   LOCATION_ID,
   BUYER_ID,
   SELLER_ID,
   INTERNAL_REFERENCE_ID,
   TERMINATION_DATE,
   CONFIRMATION_LEVEL,
   FIXED_AMOUNT_FLAG,
   CONFIRMED,
   PENDING_REQUEST_BY,
   PENDING_PROFILE,
   SUBMIT_DATE,
   LAST_UPDATED_DATE,
   FIXED_AMOUNT_PATTERN) 
Values 
  (680,
   <b>TO_DATE ('01/01/2002 4:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')</b>,
   <b>TO_DATE ('02/01/2002 4:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')</b>,
   21,
   'NA',
   'EBM',
   506,
   8,
   50010,
   'EXTREF680',
   '',
   'C',
   'N',
   'Y',
   '',
   'N',
   <b>TO_DATE ('12/30/2001 4:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')</b>,
   <b>TO_DATE ('01/01/2002 3:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')</b>,
   '');

Report message to a moderator

Re: ORA-01830 error. [message #10724 is a reply to message #10722] Thu, 12 February 2004 03:55 Go to previous message
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
Senior Member
I missed one of the dates in the above response. Please see my corrected second response.

Report message to a moderator

Previous Topic: displaying ascii
Next Topic: return a connection by parameter
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 25 09:12:11 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.