Informix Dynamic Server (IDS) Data Types - DATETIME

Versions: Informix Dynamic Server 11.x, 10.x, 9.x and 7.x

Informix - DATETIME
Syntax	DATETIME [largest_qualifier TO smallest_qualifier]
Data	Date data and time (year, month, day, hours, minutes, seconds and fraction of seconds). The largest_qualifier and smallest_qualifier can be YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, FRACTION(n), provided that smallest_qualifier does not specify a larger time unit than largest_qualifier, they can be the same, for example, DATETIME YEAR TO YEAR
Range	January 01, 0001 00:00:00.00000 to December 31, 9999 23:59:59.99999 0 ⇐ fraction ⇐ 5, default is 3

Example:

CREATE TABLE t_datetime (c1 DATETIME YEAR TO FRACTION(5));

Informix DATETIME - Equivalents in Other Databases

Database	Data Type and Conversion
Oracle	TIMESTAMP(p), January 1, 4712 BC 00:00:00.000000000 to December 31, 9999 23:59:59.999999999 (nanoseconds accuracy)
SQL Server	DATETIME, January 01, 1753 00:00:00.000 to December 31, 9999 23:59:59.997 with accuracy 1/300 second DATETIME2 and DATETIMEOFFSET, January 01, 0001 00:00:00.0000000 to December 31, 9999 23:59:59.9999999 (100 nanoseconds accuracy) SMALLDATETIME, January 01, 1900 00:00 to June 06, 2079 11:59 (minute accuracy)
MySQL	DATETIME, January 01, 1000 00:00:00 to December 31, 9999 23:59:59 (seconds accuracy)
PostgreSQL	TIMESTAMP(p), January 01, 4713 BC 00:00:00:000000 and December 31, 5874897 23:59:59:999999 (microseconds accuracy)
Sybase ASE	DATETIME, January 01, 1753 12:00:00.000 AM to December 31, 9999 11:59:59.999 PM (with accuracy 1/300 second) SMALLDATETIME, January 01, 1900 00:00 to June 06, 2079 11:59 (minute accuracy)
Teradata	TIMESTAMP(p), January 01, 0001 00:00:00.000 to December 31, 9999 23:59:61.999999 (microseconds accuracy)
HP Neoview	TIMESTAMP(p), January 01, 0001 00:00:00.000000 to December 31, 9999 23:59:59.999999 (microseconds accuracy)
Ingres	TIME(p), 00:00:00 and 24:00:00; TIMESTAMP(p), 0001-01-01 00:00:00 to 9999-12-31 24:00:00

Related Data Types in Informix

Data Types
Datetime	DATE	INTERVAL year-month	INTERVAL day-time

All Data Types

Oracle

SQL Server

IBM DB2

MySQL

PostgreSQL

Sybase ASE

Sybase ASA

Informix DS

Teradata

HP Neoview

Interbase/Firebird

HiRDB

Ingres