Home » SQL & PL/SQL » SQL & PL/SQL » TO_CHAR(TO_DATE()) error...!!!!
TO_CHAR(TO_DATE()) error...!!!! [message #248116] Thu, 28 June 2007 02:02 Go to next message
msg2ajay
Messages: 51
Registered: June 2007
Location: KUALA LUMPUR
Member
hi,
i am using the following statement......

select to_char(to_date(settdate,'YYYYMMDD'),'YYYY') from ifmr;


when i execute the query i am getting the following error....
ERROR:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0


my field 'settdate' is varchar2(8) datatype and it have the value as '20070627' and some more dates...
what is the error...
If i try the same statement with other table my statement is wroking..!!!. do any bady have idea on that error..
thx
Ajay

[Updated on: Thu, 28 June 2007 02:03]

Report message to a moderator

Re: TO_CHAR(TO_DATE()) error...!!!! [message #248118 is a reply to message #248116] Thu, 28 June 2007 02:06 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
msg2ajay wrote on Thu, 28 June 2007 09:02
it have the value as '20070627' and some more dates...
I'm interested in the 'some more dates'. There must be one that doesn't comply with your format mask.

MHE
Re: TO_CHAR(TO_DATE()) error...!!!! [message #248122 is a reply to message #248116] Thu, 28 June 2007 02:10 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

I don't have a problem in executing what u said.

create table fertest(setdate varchar2(8));
Table created.


0.01 seconds

insert into fertest values(20070627);
1 row(s) inserted.


0.01 seconds

select to_char(to_date(setdate,'YYYYMMDD'),'YYYY') from fertest;  

TO_CHAR(TO_DATE(SETDATE,'YYYYMMDD'),'YYYY') 
2007 

1 rows returned in 0.00 seconds 

    

Re: TO_CHAR(TO_DATE()) error...!!!! [message #248123 is a reply to message #248116] Thu, 28 June 2007 02:12 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
could you please provide some sample data.

--Yash
Re: TO_CHAR(TO_DATE()) error...!!!! [message #248125 is a reply to message #248118] Thu, 28 June 2007 02:17 Go to previous messageGo to next message
msg2ajay
Messages: 51
Registered: June 2007
Location: KUALA LUMPUR
Member
hi,
i have 20 rows in my table but unfortunately i have one field as empty!!! so may be because of the empty field i am getting the error.

thx
Ajay
Re: TO_CHAR(TO_DATE()) error...!!!! [message #248126 is a reply to message #248125] Thu, 28 June 2007 02:19 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

if u have only 20 rows, can u post the values in those rows?
Re: TO_CHAR(TO_DATE()) error...!!!! [message #248128 is a reply to message #248125] Thu, 28 June 2007 02:22 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
NULL can't be your problem, but if it contains blanks you have the error you described:

SQL> select to_char(to_date(' ','YYYYMMDD'),'YYYY') from dual;
select to_char(to_date(' ','YYYYMMDD'),'YYYY') from dual
                       *
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
In order to resolve that, use TRIM:
SQL> Var yourdate VARCHAR2(10)
SQL>
SQL> -- Assign a blank:
SQL> EXEC :yourdate := ' ';

PL/SQL procedure successfully completed.

SQL>
SQL> -- Error:
SQL> select to_char(to_date(:yourdate,'YYYYMMDD'),'YYYY') from dual;
select to_char(to_date(:yourdate,'YYYYMMDD'),'YYYY') from dual
                        *
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0


SQL>
SQL> -- No error:
SQL> select to_char(to_date(TRIM(:yourdate),'YYYYMMDD'),'YYYY') from dual;

TO_C
----



MHE
Re: TO_CHAR(TO_DATE()) error...!!!! [message #248129 is a reply to message #248128] Thu, 28 June 2007 02:25 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

Blanks?... What is a blank?. how it differs from null?

Re: TO_CHAR(TO_DATE()) error...!!!! [message #248130 is a reply to message #248126] Thu, 28 June 2007 02:29 Go to previous messageGo to next message
msg2ajay
Messages: 51
Registered: June 2007
Location: KUALA LUMPUR
Member
hi,
yaa sure these are the rows presented in my table;

SQL> select settdate,actno from ifmr;

SETTDATE ACTNO
-------- --------------
20070401 00062000001611
20050601 00062000001911
20070612 00062500001211
20040204 00062200001411
20030401 00062200001502
20080605 00062100000402
20090606 00062700001811
20030201 00062500002011
20040407 00062900002111
20050601 00062600002311
         00062200002211

SETTDATE ACTNO
-------- --------------
20070601 00062300001711
20070604 00062500000502
20070601 00062900000602
20030604 00062600000711
20070905 00062000000811
20070601 00062300000911
20071012 00062400001002
20070201 00062100001111
20050321 00062200004334

20 rows selected.


As in my table '00062200002211' is empty so i am getting the error.
ThxQ very much for response from u all,
Ajay.

Re: TO_CHAR(TO_DATE()) error...!!!! [message #248140 is a reply to message #248129] Thu, 28 June 2007 02:54 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
caliguardo wrote on Thu, 28 June 2007 09:25
Blanks?... What is a blank?. how it differs from null?

A blank is ascii character 32. A space. Here$are$blanks$replaced$with$dollar$signs. If a varchar2 contains nothing but blanks it appears to be empty but it contains data.

Here's a test script:


col yourdate format a10
set feedb 1

WITH yourtable AS
  (
    SELECT NULL yourdate FROM dual UNION ALL
    SELECT ' '  yourdate FROM dual
  )
SELECT yourdate 
FROM   yourtable
/

WITH yourtable AS
  (
    SELECT NULL yourdate FROM dual UNION ALL
    SELECT ' '  yourdate FROM dual
  )
SELECT to_char(to_date(yourdate ,'YYYYMMDD'),'YYYY') theyear
FROM   yourtable
/

SET NULL '<NULL>'

WITH yourtable AS
  (
    SELECT NULL yourdate FROM dual UNION ALL
    SELECT ' '  yourdate FROM dual
  )
SELECT yourdate 
FROM   yourtable
/


If you execute it, you get:
SQL> col yourdate format a10
SQL> set feedb 1
SQL>
SQL> WITH yourtable AS
  2    (
  3      SELECT NULL yourdate FROM dual UNION ALL
  4      SELECT ' '  yourdate FROM dual
  5    )
  6  SELECT yourdate
  7  FROM   yourtable
  8  /

YOURDATE
----------



2 rows selected.

SQL>
SQL> WITH yourtable AS
  2    (
  3      SELECT NULL yourdate FROM dual UNION ALL
  4      SELECT ' '  yourdate FROM dual
  5    )
  6  SELECT to_char(to_date(yourdate ,'YYYYMMDD'),'YYYY') theyear
  7  FROM   yourtable
  8  /
ERROR:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0



no rows selected

SQL>
SQL> SET NULL '<NULL>'
SQL>
SQL> WITH yourtable AS
  2    (
  3      SELECT NULL yourdate FROM dual UNION ALL
  4      SELECT ' '  yourdate FROM dual
  5    )
  6  SELECT yourdate
  7  FROM   yourtable
  8  /

YOURDATE
----------
<NULL>


2 rows selected.

SQL>


NULL is the absence of a value.

MHE
Previous Topic: Execute Immediate
Next Topic: What r the ways of reducing cost & time?
Goto Forum:
  


Current Time: Thu Dec 08 10:08:21 CST 2016

Total time taken to generate the page: 0.12661 seconds