The date and time format (datetimeformat) specifies the way in which date values, time values, and timestamp values are represented.
<datetimeformat> ::= EUR | INTERNAL | ISO | JIS | USA
Date Value
'YYYY' |
Four-digit year format |
'MM' |
Two-digit month format (01-12) |
'DD' |
Two-digit day format (01-31) |
Format |
General Form |
Example |
EUR |
'DD.MM.YYYY' |
'23.01.1999' |
INTERNAL |
'YYYYMMDD' |
'19990123' |
ISO/JIS |
'YYYY-MM-DD' |
'1999-01-23' |
USA |
'MM/DD/YYYY' |
'01/23/1999' |
In all formats, with the exception of INTERNAL, leading zeros may be omitted in the identifiers for the month and day.
'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 Form |
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 identifier of the hour must consist of at least one digit. In the USA time format, 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 INTERNAL format, you can use up to 9999 hours.
'YYYY' |
Four-digit year format |
'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 Form |
Example |
EUR/JIS/USA |
'YYYY-MM-DD-HH.MM.SS.MMMMMM' |
'1999-01-23-14.30.08.456234' |
ISO |
'YYYY-MM-DD HH:MM:SS.MMMMMM' |
'1999-01-23 14:30:08.456234' |
INTERNAL |
'YYYYMMDDHHMMSSMMMMMM' |
'19990123143008456234' |
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.
The date and time format is determined during the installation of the database system by the special database parameter DATE_TIME_FORMAT.
Each user can change the date and time format for their own session by making the appropriate entries in the database tool (for example, in the Loader: standard_date_mask, standard_time_mask, standard_timestamp_mask) or by entering the corresponding specifications in SQL statements (datetimeformat).
The date and time format in SQL statements 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.
The ISO date and time format is used by ODBC and JDBC applications and cannot be replaced with a different date and time format.