ORA-01841 on date column

[JPDB_75]

Version : Oracle 8
OS : Solaris

I have a table containing a date column. On one of the records I have the following problems :

SQL> alter session set nls_date_format = 'dd/mm/yyyy';

Session altered.

SQL> select date_ouverture from contrat59.t_pc_element_service_commande where id_element_service_commande= 11983;

DATE_OUVER
----------
27/01/5555

SQL> select to_char(date_ouverture,'YYYYMMDD') from contrat59.t_pc_element_service_commande where id_element_service_commande= 11983;

TO_CHAR(
--------
00000000

SQL> select date_ouverture+1 from contrat59.t_pc_element_service_commande where id_element_service_commande= 11983;

ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0


Can somebody explain what's going on ?

[hrishy]

Hi

Whats the output of

desc t_pc_element_service_commande

regards
Hrishy

[JPDB_75]

Hi

Whats the output of

desc t_pc_element_service_commande

regards
Hrishy

SQL> desc contrat59.t_pc_service_commande
Name Null? Type
----------------------------------------- -------- ----------------------------
ID_CLASSE_SERVICE NOT NULL NUMBER(15)
ID_SERVICE_GENESE NUMBER(15)
ID_SERVICE_SOURCE NUMBER(15)
ID_SERVICE_CONTAINER NUMBER(15)
ID_TYPE_IDENTIFIANT NUMBER(15)
ID_MODELE_ANNEXE_TECH NUMBER(15)
TYPE_SERVICE NOT NULL NUMBER(4)
TYPE_GESTION NOT NULL NUMBER(4)
VERSION_SERVICE NOT NULL NUMBER(4)
CODE_SERVICE NOT NULL VARCHAR2(50)
LIBELLE NOT NULL VARCHAR2(100)
FLAG_IDENTIQUE NOT NULL NUMBER(4)
DATE_OUVERTURE NOT NULL DATE
DATE_FERMETURE DATE
DATE_RETABLISSEMENT DATE
DATE_SUSPENSION DATE
MOTIF_SUSPENSION VARCHAR2(100)
REFERENCE_INTERNE VARCHAR2(50)
LIBELLE_INTERNE VARCHAR2(100)
NOMBRE_SITE NOT NULL NUMBER(4)
FLAG_EN_NOMBRE NOT NULL NUMBER(4)
FLAG_PARC NOT NULL NUMBER(4)
DESCRIPTION_GENERALE VARCHAR2(500)
FLAG_UNIQUE_PAR_SITE NOT NULL NUMBER(4)
LIBELLE_REF_COM_FOURNISSEUR VARCHAR2(100)
MODE_AFFICHAGE_RCF NOT NULL NUMBER(4)
LIBELLE_REF_COM_CLIENT VARCHAR2(100)
MODE_AFFICHAGE_RCC NOT NULL NUMBER(4)
LIBELLE_REF_FOURNISSEUR VARCHAR2(100)
MODE_AFFICHAGE_RF NOT NULL NUMBER(4)
LIBELLE_REF_CLIENT VARCHAR2(100)
MODE_AFFICHAGE_RC NOT NULL NUMBER(4)
AFFICHAGE_CODE NOT NULL NUMBER(1)
CODE_LANGUE VARCHAR2(3)
ID_SERVICE_COMMANDE NOT NULL NUMBER(15)
ID_COMMANDE NOT NULL NUMBER(15)
ID_SERVICE_PARC NUMBER(15)
ID_SERVICE_CATALOGUE NUMBER(15)
ID_SERVICE_CONTENANT_PARC NUMBER(15)
ID_SERVICE_CONTENANT_COMMANDE NUMBER(15)
ID_ENTITE NUMBER(15)
QUANTITE_COMMANDE NOT NULL NUMBER(10,2)
REFERENCE_CLIENT VARCHAR2(50)
REFERENCE_FOURNISSEUR VARCHAR2(50)
VALEUR_IDENTIFIANT VARCHAR2(50)
ID_ANNEXE_TECH NUMBER(15)
ID_CLIENT NUMBER(15)
ID_CDR NUMBER(15)
ID_SITE NUMBER(15)
TYPE_COMMANDE NUMBER(4)
ETAT_COMMANDE NUMBER(4)
CDR_REPARTITION NUMBER(5,2)
ETAPE_COMMANDE NUMBER(15)
TARIF NUMBER(15,4)
ETAT NUMBER(15)
STATUT NUMBER(15)
TYPE_PRESTATION NUMBER(4)

[gamyers]

And how about
SELECT DUMP(date_ouverture) from contrat59.t_pc_element_service_commande where id_element_service_commande= 11983;

[JPDB_75]

And how about
SELECT DUMP(date_ouverture) from contrat59.t_pc_element_service_commande where id_element_service_commande= 11983;

Since we had a problem with this row , we updated it and I do not have the value in date_ouverture which gave a problem.

However , we identified the source of the problem.

This value was updated using java.

The value which was entered was 27/01/20055.

What I do not unterstand is that Oracle found a way to enter the value in the colum but can not make any operation on it.

[jmodic]

Since we had a problem with this row , we updated it and I do not have the value in date_ouverture which gave a problem.

However , we identified the source of the problem.

This value was updated using java.

The value which was entered was 27/01/20055.

What I do not unterstand is that Oracle found a way to enter the value in the colum but can not make any operation on it.

But still, I'm very curious what that dump shows - what is the content of the date column's 7 bytes? Can you please execute the command that gamyers provided in SQL*Plus and copy-paste the result here?

[JPDB_75]

But still, I'm very curious what that dump shows - what is the content of the date column's 7 bytes? Can you please execute the command that gamyers provided in SQL*Plus and copy-paste the result here?

here is the result :

Typ=12 Len=7: 120,104,2,17,1,1,1

[jmodic]

here is the result :

Typ=12 Len=7: 120,104,2,17,1,1,1

Something does not mach here! You said the date enetered was something like '27/01/20055' and that query on that value returned '27/01/5555'. However your dump shows a perfectly normal date that does not match any of those two - it represents the date 17/02/2004 00:00:00 (dd/mm/yyyy hh24:mi:ss). So neither the day nor month portion match any of your quoted dates and the year in this dump is perfectly normal 2004. !?