Informix

Expand all | Collapse all

TIMEDATE field problem

  • 1.  TIMEDATE field problem

    Posted 30 days ago

    Hi,

    I am trying to load values for one table with DATETIME field, even defining the DBDATE format the problem continues giving 1263 error...


    1263: A field in a datetime or interval value is incorrect or an illegal operation
    specified on datetime field.

    my table

    create table bi_spoproc(
    id_proc integer not null,
    class varchar(2),
    ano integer,
    num integer,
    sub varchar(1),
    dtentrada datetime year to second,
    proced integer,
    tpent integer,
    instplan varchar(150),
    classespaco varchar(150),
    freguesia varchar(150),
    primary key (id_proc)
    constraint PK_BI_SPOPROC
    );

    I have tried classic load from insert into, also tried to use external table
    CREATE EXTERNAL TABLE bi_spoprocext
    SAMEAS bi_spoproc
    USING (
    DATAFILES ( "DISK:/home/informix/airc/processos.UNL")
    );

    INSERT INTO bi_spoproc SELECT * FROM bi_spoprocext;

    But still having the 1263 error??!!

    My load file have this format
    31181|1 |2019|2|0|02-01-2019 00:00:00|5|0|Área Urbana de Génese Ilegal/Alvará de Loteamento||São Domingos de Rana|
    31182|1 |2019|3|0|02-01-2019 00:00:00|5|0|Alvará de Loteamento||União das freguesias de Carcavelos e Parede|
    31183|1 |2019|4|0|02-01-2019 00:00:00||0|Área Urbana de Génese Ilegal/Plano Director Municipal||São Domingos de Rana|
    31184|1 |2019|5|0|02-01-2019 00:00:00|1|0|Alvará de Loteamento||União das freguesias de Cascais e Estoril|
    31185|32|2019|6|0|02-01-2019 00:00:00|1|0|Plano Director Municipal||Alcabideche|
    31186|1 |2019|7|0|02-01-2019 00:00:00|5|0|Área Urbana de Génese Ilegal/Alvará de Loteamento||São Domingos de Rana|
    31187|1 |2019|8|0|02-01-2019 00:00:00|1|0|Área Urbana de Génese Ilegal/Alvará de Loteamento||Alcabideche|

    Please, can someone help me?

    Thanks,

    SP


    ------------------------------
    Sergio Peres
    AIRC
    Coimbra
    ------------------------------


  • 2.  RE: TIMEDATE field problem

    Posted 30 days ago
    DBDATE is for the type date.

    datetimes can only be used with YYYY-MM-DD HH:MM:SS.?????
    to whatever precision you have defined it as


    Clive Eisen
    GPG: 3818B5F1











  • 3.  RE: TIMEDATE field problem

    Posted 30 days ago

    Your date is formatted as D-M-Y4 it needs to be Y4-M-D for datetime

     

    You can something clever within the select statement using substr/extend/cast etc but easier to format the raw data correctly

     

    Cheers

    Paul






  • 4.  RE: TIMEDATE field problem

    Posted 30 days ago

    Hi Sergio.

    Or, to avoid having to change your data files:
    https://www.ibm.com/docs/en/informix-servers/14.10?topic=variables-gl-datetime-environment-variable

    This should do it for European date format (untested):
    export GL_DATETIME='%d-%m-%Y %H:%M:%S'



    ------------------------------
    Doug Lawry
    Oninit Consulting
    ------------------------------



  • 5.  RE: TIMEDATE field problem

    Posted 30 days ago
    Yes, I know the difference but as I don't find the reason for failure I try to use it...

    ------------------------------
    Sergio Peres
    AIRC
    Coimbra
    ------------------------------



  • 6.  RE: TIMEDATE field problem

    Posted 30 days ago

    You are using

     

    02-01-2019 00:00:00

     

    The engine is expecting

     

    2019-01-02 00:00:00  (or maybe 2019-02-01 00:00:00)

     

    As Doug suggests you can use GL_DATETIME, not sure if this will kick you else where

    Cheers

    Paul

     






  • 7.  RE: TIMEDATE field problem

    Posted 30 days ago

    Thanks for replies,

    I have tried GL_DATETIME and with USE_DTENV=1 works like a charm.

    Thanks everyone for the help,

    Best regards,

    SP



    ------------------------------
    Sergio Peres
    AIRC
    Coimbra
    ------------------------------



  • 8.  RE: TIMEDATE field problem

    Posted 30 days ago

    If you can't change the date format in your file and you can't (or don't want to) change the date format in the database, I would load the file to a temp table, with the date field as a varchar, then parse it into the correct format to load a real table.

     

                            --EEM

     






  • 9.  RE: TIMEDATE field problem

    Posted 30 days ago
    Sergio - thank you for sharing the solution and use of the USE_DTENV environment variable - that was the missing link!  I didn't know about this option, so thanks!

    ------------------------------
    Mike Walker
    xDB Systems, Inc
    www.xdbsystems.com
    ------------------------------