ORA-01841 on date column
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: ORA-01841 on date column

  1. #1
    Join Date
    Jun 2001
    Posts
    32

    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 ?

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Whats the output of

    desc t_pc_element_service_commande

    regards
    Hrishy

  3. #3
    Join Date
    Feb 2005
    Posts
    158
    And how about
    SELECT DUMP(date_ouverture) from contrat59.t_pc_element_service_commande where id_element_service_commande= 11983;

  4. #4
    Join Date
    Jun 2001
    Posts
    32
    Quote 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)

  5. #5
    Join Date
    Jun 2001
    Posts
    32
    Quote 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.

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Quote 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?

  7. #7
    Join Date
    Jun 2001
    Posts
    32
    Quote 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

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Quote 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?

  9. #9
    Join Date
    Jun 2001
    Posts
    32
    Quote 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
  •  


Click Here to Expand Forum to Full Width