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!
Original Message:
Sent: Thu September 23, 2021 12:06 PM
From: Sergio Peres
Subject: TIMEDATE field problem
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
Original Message:
Sent: Thu September 23, 2021 09:21 AM
From: Doug Lawry
Subject: TIMEDATE field problem
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
Original Message:
Sent: Thu September 23, 2021 07:42 AM
From: Sergio Peres
Subject: 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 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
------------------------------
#Informix