CODEWITHSUNDEEP
sqldatetimeinformix
stb
stb

Reputation: 3513

Inserting datetime values into an informix table fails

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

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.

Upvotes: 4

Views: 25909

Answers (2)

RET
RET

Reputation: 9188

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.

Upvotes: 4

Joe R.
Joe R.

Reputation: 2042

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

Upvotes: 5

Related Questions