Inserting datetime values into an informix table fails

Question

I am trying to insert a test value '10.06.2011 10:24' into an informix 7.3(sadly) database. The destination column is

• System.DateTime
• length:16
• precision 10

while these work

select  to_date('10.06.2011 10:24', "%d.%m.%Y %H:%M") from test;
INSERT into test values('10.06.2011')

these aren't

insert into test values('10.06.2011 10:24');
insert into test values(to_date('10.06.2011 10:24:00', "%d.%m.%Y %H:%M"))

I tried several more thing like adding secounds but none if it worked. All i get as error is a syntax error without any detail on where it is.

Any suggestions are appreciated.

Answer 1

The format of your date part, 10.06.2011, you're trying to insert into the DATETIME column is wrong.

The accepted format, using your example, is: 2011-10-06 10:24:00

Answer 2

You can manipulate how DATE type fields are presented and interpreted with the DBDATE environment variable. Setting it to "DMY4." would format the dates as you've shown them.

But as Frank has said, the format of DATETIME is fixed: it must be 'YYYY-MM-DD hh:mm:ss.fffff' or part thereof, depending on the scale and precision of the DATETIME column in question.

UPDATE (the 2nd comment was getting a bit long...)

I'm troubled by your declaration of the format of the 'destination column'. If you run the following:

dbschema -d <database> -t <table>

... the result will tell you the column definition as Informix understands it. Is it a DATE or a DATETIME ?

You can insert a DATETIME value into a DATE column (the time gets truncated), and you can insert a DATE into a DATETIME column (time defaults to midnight). But the value must be able to be coerced into the target column format, and DD.MM.YYYY hh:mm is not valid for a DATETIME.