DATE and DATETIME data types

Informix^® provides two data types to store date and time information:

DATE = for year, month and day storage.
DATETIME = for year to fraction(1-5) storage.

SQLite 3 does not have a native type for date/time storage, but you can use data/time type names and functions based on the string representation of dates and times. The date/time values are stored in the TEXT native type. The date/time functions of SQLite are based on standard DATE (YYYY-MM-DD), TIME (hh:mm:ss) and TIMESTAMP (YYYY-MM-DD hh:mm:ss) concepts.

For maximum flexibility with other RDBMS SQL languages, SQLite allows to define table columns with your own type names. You can for example use the SMALLDATETIME, SMALLTIME, TIME(N), DATETIME(N) type names.

Solution

The following conversions are done by the ODI SQLite driver for date/time types:

DATE type is not translated, it will be used as is by SQLite.
DATETIME HOUR TO MINUTE is translated to SMALLTIME.
DATETIME HOUR TO SECOND is translated to TIME.
DATETIME HOUR TO FRACTION(n) is transalated to TIME(n).
DATETIME YEAR TO DAY is translated to TINYDATETIME.
DATETIME YEAR TO MINUTE is translated to SMALLDATETIME.
DATETIME YEAR TO SECOND is translated to DATETIME.
DATETIME YEAR TO FRACTION(n) is translated to DATETIME(n).
DATETIME with another precision as above are translated to TIMESTAMP.

See also Date and time in SQL statements for good SQL programming practices.