Date and Time Format (datetimeformat) 
The data and time format (datetimeformat) specifies the way in which date values, time values and time stamp values are represented.
In SQL statements, it determines the format in which the date, time, and timestamp values can be represented in these SQL statements, and the way in which results are displayed.
Syntax
<datetimeformat> ::= EUR | INTERNAL | ISO | JIS | USA
The ISO date and time format is used by ODBC and JDBC applications and cannot be replaced with a different date and time format.
Date Value
'YYYY' |
Four-digit year format (0001-9999) |
'MM' |
Two-digit month format (01-12) |
'DD' |
Two-digit day format (01-31) |
Format |
General Format |
Example |
EUR |
'DD.MM.YYYY' |
'23.01.2005' |
INTERNAL |
'YYYYMMDD' |
'20050123' |
ISO/JIS |
'YYYY-MM-DD' |
'2005-01-23' |
USA |
'MM/DD/YYYY' |
'01/23/2005' |
In all formats, with the exception of INTERNAL, leading zeros may be omitted in the identifiers for the month and day.
Time Value
'HHHH' |
Four-digit hour format |
'HH' |
Two-digit hour format |
'MM' |
Two-digit minute format (00-59) |
'SS' |
Two-digit second format (00-59) |
Format |
General Format |
Example |
EUR |
'HH.MM.SS' |
'14.30.08' |
INTERNAL |
'HHHHMMSS' |
'00143008' |
JIS/ISO |
'HH:MM:SS' |
'14:30:08' |
USA |
'HH:MM AM (PM)' |
'2:30 PM' |
In all time formats, the hour identifier must consist of at least one digit. In the time format for the USA, the minute identifier can be omitted completely. In all the other formats, with the exception of INTERNAL, the minute and second identifiers must comprise at least one digit.
In the ISO
and USA formats, the hours are displayed modulo 24.
In the EUR and JIS formats, a number of hours greater than 99 causes an
error.
In the format INTERNAL, you can specify hours up to 9999.
Timestamp Value
'YYYY' |
Four-digit year format (0001-9999) |
'MM' |
Two-digit month format (01-12) |
'DD' |
Two-digit day format (01-31) |
'HH' |
Two-digit hour format (0-24) |
'MM' |
Two-digit minute format (00-59) |
'SS' |
Two-digit second format (00-59) |
'MMMMMM' |
Six-digit microsecond format |
Format |
General Format |
Example |
EUR/JIS/USA |
'YYYY-MM-DD-HH.MM.SS.MMMMMM' |
'2005-01-23-14.30.08.456234' |
ISO |
'YYYY-MM-DD HH:MM:SS.MMMMMM' |
'2005-01-23 14:30:08.456234' |
INTERNAL |
'YYYYMMDDHHMMSSMMMMMM' |
'20050123143008456234' |
The microsecond identifier can be omitted in all timestamp formats. In all formats, with the exception of INTERNAL, the month and day identifiers must consist of at least one digit.
See also:
Special Database Parameter DATE_TIME_FORMAT