Background documentationDate and Time Format (datetimeformat) Locate this document in the navigation structure

 

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.

Structure

Syntax Syntax

  1. <datetimeformat> ::=
  EUR
| INTERNAL
| ISO
| JIS
| USA
End of the code.

Note Note

The ISO date and time format is used by ODBC and JDBC applications and cannot be replaced with a different date and time format.

End of the note.
Date Value
Date and Time Format (datetimeformat)

'YYYY'

Four-digit year format (0001-9999)

'MM'

Two-digit month format (01-12)

'DD'

Two-digit day format (01-31)
Date and Time Format (datetimeformat)

Format

General Format

Example

EUR

'DD.MM.YYYY'

'23.01.2008'

INTERNAL

'YYYYMMDD'

'20080123'

ISO/JIS

'YYYY-MM-DD'

'2008-01-23'

USA

'MM/DD/YYYY'

'01/23/2008'

In all formats, with the exception of INTERNAL, leading zeros may be omitted in the identifiers for the month and day.

Time value

'HHHH'/'HH'

Four-digit/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'

ISO/JIS

'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 INTERNAL format, you can specify up to 9999 hours.

Time Stamp Value

'YYYY'

Four-digit year format (0001-9999)

'MM'/'DD'

Two-digit month format (01-12)/day format (01-31)

'HH'/'MM'/'SS'

Two-digit hour format (00-24)/Minute format (00-59)/Second format (00-59)

'MMMMMM'

Six-digit microsecond format

Format

General Format

Example

EUR/JIS/USA

'YYYY-MM-DD-HH.MM.SS.MMMMMM'

'2008-01-23-14.30.08.456234'

ISO

'YYYY-MM-DD HH:MM:SS.MMMMMM'

'2008-01-23 14:30:08.456234'

INTERNAL

'YYYYMMDDHHMMSSMMMMMM'

'20080123143008456234'

The microsecond identifier can be omitted in all time stamp formats. In all formats, with the exception of INTERNAL, the month and day identifiers must consist of at least one digit.

More Information

Special Database Parameters DateTimeFormat

CHAR(a,t)