DATE: Formatting
Goal: using the SQL-99 standard format (YYYY-MM-DD) for both input and output.
| Database | System Format | SELECT Query Format |
|---|---|---|
| Access 2000 | n/a | FORMAT(col, 'yyyy-mm-dd') |
| DB2 8.1 | n/a | n/a |
| 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'
Don't do this if also dealing with TIMESTAMP columns |
TO_CHAR(col, 'YYYY-MM-DD')
Not necessary if NLS_DATE_FORMAT is set to YYYY-MM-DD |
| PostgreSQL 7.4 |
SET DATESTYLE = 'ISO'
This is the default |
n/a |
| SQL Server 2000 |
SET DATEFORMAT ymd
Ensures interpretation of input |
CONVERT(CHAR(10), col, 120) |
| 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), '.', '-') |