TIMESTAMP: Formatting
Goal: using the SQL-99 standard format (YYYY-MM-DD HH:MM:SS) for both input and output.
| Database | System Format | SELECT Query Format | |
|---|---|---|---|
| Access 2000 | n/a | n/a | |
| DB2 8.1 | n/a | TO_CHAR(col, 'YYYY-MM-DD HH24:MI:SS') | |
| Firebird 1.5 | n/a | n/a | |
| FrontBase 4.1 | n/a | n/a | |
| MySQL 4.0 – 4.1 | n/a | n/a | |
| Oracle 9.2 | ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS' | n/a | |
| PostgreSQL 7.4 |
SET DATESTYLE = 'ISO'
This is the default |
n/a | |
| SQL Server 2000 |
SET DATEFORMAT ymd
Ensures interpretation of input |
CONVERT(CHAR(19), col, 120)
ini_set('mssql.datetimeconvert', 'Off') is buggy in older versions of PHP |
|
| SQLite 2.8 | n/a | n/a | |
| Sybase ASE 12.5 |
SET DATEFORMAT ymd
Ensures interpretation of input |
STR_REPLACE(CONVERT( CHAR(10), col, 102), '.', '-') + ' ' + CONVERT(CHAR(8), col, 20) |