-
ORA-01841 on date column
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 ?
-
Hi
Whats the output of
desc t_pc_element_service_commande
regards
Hrishy
-
And how about
SELECT DUMP(date_ouverture) from contrat59.t_pc_element_service_commande where id_element_service_commande= 11983;
-
Originally Posted by hrishy
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)
-
Originally Posted by gamyers
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.
-
Originally Posted by JPDB_75
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?
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Originally Posted by jmodic
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
-
Originally Posted by JPDB_75
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. !?
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Originally Posted by jmodic
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. !?
Like I mentionned above we changed the value of the column.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|