TIMEDATE field problem

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 operationspecified 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 ------------------------------ #Informix

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

Yes, I know the difference but as I don't find the reason for failure I try to use it... ------------------------------ Sergio Peres AIRC Coimbra ------------------------------

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

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 ------------------------------

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

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

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 ------------------------------

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 ------------------------------