Inserting datetime values into an informix table fails
datetime, informix, sql
Solution
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`.
Problem
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.