Database Portability: Date and Timestamp Columns

Dates present the biggest stumbling block to writing applications capable of using a wide variety of Database Management Systems (DBMS's).

This page provides a quick index of which data types are used for storing date/timestamp/datetime information and how to get each DBMS to accept ISO formatted input and produce ISO formatted output.

By "ISO format" I mean the ISO 8601 formats specified by the SQL:1999 standard. For DATE literals that's DATE yyyy-mm-dd. For TIMESTAMP literals TIMESTAMP yyyy-mm-dd hh:mm:ss is used.

The minimums and maximums shown in the tables below indicate those specified in the documentation. If tests allowed different values, a note is provided.

When a DBMS allows BC dates, this tutorial will include two rows. The first contains information for positive dates only in order to keep the format in tact. The second row indicates how to take advantage sub-zero dates.

TIMESTAMP
DBMS	Data Type	System Format	Query Format	Minimum	Maximum	Bytes
SQL:1999	TIMESTAMP	n/a	n/a	0001-01-01 00:00:00	9999-12-31 23:59:62	n/a
DB2 8.1	TIMESTAMP	n/a	TO_CHAR(col, 'YYYY-MM-DD HH24:MI:SS')	0001-01-01 00:00:00	9999-12-31 23:59:59	10
FrontBase 4.1	TIMESTAMP	n/a	n/a	1600-01-01 00:00:00 Literals must be prefaced by the word `TIMESTAMP`	9999-12-31 23:59:62 Literals must be prefaced by the word `TIMESTAMP`	8
Interbase 7.1	TIMESTAMP	SET SQL DIALECT 3; This is the default in 7.1	n/a	0100-01-01 00:00:00 Test allowed 0001-01-01 00:00:00	32768-02-29 23:59:59 Test only allowed 9999-12-31 23:59:59	8
MySQL 3.23, 4.0	DATETIME	n/a	n/a	1000-01-01 00:00:00 Test allowed 0001-01-01 00:00:00	9999-12-31 23:59:59	8
MySQL 3.23, 4.0	TIMESTAMP	n/a	DATE_FORMAT (col, '%Y-%m-%d %T') Not necessary in MySQL >= 4.1	1970-01-01 00:00:00	2037-12-31 23:59:59	4
Oracle 9.2	DATE	ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'; Required for input	TO_CHAR (col, 'YYYY-MM-DD HH24:MI:SS') Not needed if NLS_DATE_FORMAT set	0001-01-01 00:00:00	4712-12-31 23:59:59 Test allowed 9999-12-31 23:59:59	7
Oracle 9.2	DATE	ALTER SESSION SET NLS_DATE_FORMAT = 'SYYYY-MM-DD HH24:MI:SS'; Required for input The "S" format element extends the range of usable dates by prefixing BC dates with "-"	TO_CHAR (col, 'SYYYY-MM-DD HH24:MI:SS') Not needed if NLS_DATE_FORMAT set	-4712-01-01 00:00:00	4712-12-31 23:59:59 Test allowed 9999-12-31 23:59:59 Output of AD dates is prefixed by a space	7
Oracle 9.2	TIMESTAMP(0) Introduced in 9i	ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'SYYYY-MM-DD HH24:MI:SS'; Required for input The "S" format element extends the range of usable dates by prefixing BC dates with "-"	TO_CHAR (col, 'SYYYY-MM-DD HH24:MI:SS') Not needed if NLS_TIMESTAMP_FORMAT set	-4712-01-01 00:00:00	4712-12-31 23:59:59 Test allowed 9999-12-31 23:59:59 Output of AD dates is prefixed by a space	7
PostgreSQL 7.4	TIMESTAMP(0)	SET DATESTYLE = 'ISO'; This is the default in 7.4	TO_CHAR (col, 'YYYY-MM-DD HH24:MI:SS') Not needed if DATESTYLE is ISO	0001-01-01 00:00:00	5874897-12-31 23:59:59	8
PostgreSQL 7.4	TIMESTAMP(0)	SET DATESTYLE = 'ISO'; This is the default in 7.4	TO_CHAR (col, 'YYYY-MM-DD HH24:MI:SS BC') Not needed if DATESTYLE is ISO	4713-01-01 00:00:00 BC Test allowed 4714-11-24 00:00:00 BC	5874897-12-31 23:59:59 AD The BC pattern modifier adds "AD" to the output	8
SQL Server 2000	DATETIME	SET DATEFORMAT ymd; Ensures interpretation of input	CONVERT (CHAR(19), col, 120)	1753-01-01 00:00:00	9999-12-31 23:59:59	8
Sybase ASE 12.5.1	DATETIME	SET DATEFORMAT ymd; Ensures interpretation of input	STR_REPLACE( CONVERT( CHAR(10), col, 102), '.', '-') + ' ' + CONVERT( CHAR(8), col, 20)	1753-01-01 00:00:00	9999-12-31 23:59:59	8

DATE
DBMS	Data Type	System Format	Query Format	Minimum	Maximum	Bytes
SQL:1999	DATE	n/a	n/a	0001-01-01	9999-12-31	n/a
DB2 8.1	DATE	n/a	n/a	0001-01-01	9999-12-31	4
FrontBase 4.1	DATE	n/a	n/a	1600-01-01 Literals must be prefaced by the word `DATE`	9999-12-31 Literals must be prefaced by the word `DATE`	8
Interbase 7.1	DATE	SET SQL DIALECT 3; This is the default in 7.1	n/a	0100-01-01 Test allowed 0001-01-01	32768-02-29 Test only allowed 9999-12-31	4
MySQL 3.23, 4.0	DATE	n/a	n/a	1000-01-01 Test allowed 0001-01-01	9999-12-31	3
Oracle 9.2	DATE	ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';	TO_CHAR (col, 'YYYY-MM-DD')	0001-01-01	4712-12-31 Test allowed 9999-12-31	7
Oracle 9.2	DATE	ALTER SESSION SET NLS_DATE_FORMAT = 'SYYYY-MM-DD'; Required for input The "S" format element extends the range of usable dates by prefixing BC dates with "-"	TO_CHAR (col, 'SYYYY-MM-DD') Not needed if NLS_DATE_FORMAT set	-4712-01-01	4712-12-31 Test allowed 9999-12-31 Output of AD dates is prefixed by a space	7
PostgreSQL 7.4	DATE	SET DATESTYLE = 'ISO'; This is the default in 7.4	TO_CHAR (col, 'YYYY-MM-DD') Not needed if DATESTYLE is ISO	0001-01-01	32767-12-31 Test allowed 11754179-08-04	4
PostgreSQL 7.4	DATE	SET DATESTYLE = 'ISO'; This is the default in 7.4	TO_CHAR (col, 'YYYY-MM-DD BC') Not needed if DATESTYLE is ISO	4713-01-01 BC Test allowed 4801-03-01 BC	32767-12-31 AD Test allowed 11754179-08-04 The BC pattern modifier adds "AD" to the output	4
SQL Server 2000	DATETIME	SET DATEFORMAT ymd; Ensures interpretation of input	CONVERT (CHAR(10), col, 120)	1753-01-01	9999-12-31	8
SQL Server 2000	SMALLDATETIME	SET DATEFORMAT ymd; Ensures interpretation of input	CONVERT (CHAR(10), col, 120)	1900-01-01	2079-06-06	4
Sybase ASE 12.5.1	DATE	SET DATEFORMAT ymd; Ensures interpretation of input	STR_REPLACE( CONVERT( CHAR(10), col, 102), '.', '-')	1753-01-01	9999-12-31	8
Sybase ASE 12.5.1	SMALLDATETIME	SET DATEFORMAT ymd; Ensures interpretation of input	STR_REPLACE( CONVERT( CHAR(10), col, 102), '.', '-')	1900-01-01	2079-06-06	4

More Information

If you're interested in learning more about writing portable database code, I encourage you to read SQL-99 Complete, Really and SQL Performance Tuning by Peter Gulutzan and Trudy Pelzer.