Informix

Expand all | Collapse all

TIMEDATE field problem

  • 1.  TIMEDATE field problem

    Posted Thu September 23, 2021 07:43 AM

    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 Thu September 23, 2021 07:49 AM
    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 Thu September 23, 2021 09:03 AM

    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 Thu September 23, 2021 09:22 AM

    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 Thu September 23, 2021 08:59 AM
    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 Thu September 23, 2021 10:05 AM

    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 Thu September 23, 2021 12:07 PM

    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 Thu September 23, 2021 10:26 AM

    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 Thu September 23, 2021 12:11 PM
    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
    ------------------------------