6.3.3 Predefined character-string representations
A character string literal in the format of the corresponding predefined character-string representation can be used to represent a date literal, time literal, or time stamp literal. This section describes the predefined character-string representations of dates, times, and time stamps.
- Organization of this subsection
(1) Predefined character-string representation of dates
The predefined character-string representations of dates include a predefined input representation and a predefined output representation.
(a) Predefined input representation
A character string literal that follows the format of the predefined input representation for dates can be used as a literal representing a date. The format of the predefined input representation of a date is as follows.
- Format of the predefined input representation:
-
'YYYY-MM-DD' or 'YYYY/MM/DD'
-
The year is expressed in four digits (YYYY), and the month (MM) and day (DD) in two digits. Pad the fields with zeros on the left, as necessary.
-
Specify values for YYYY, MM, and DD that are valid for the DATE type (for example, MM must be 01 to 12).
-
- Example:
-
July 30, 2013 is expressed as follows.
-
Character string literal (predefined input representation): '2013-07-30' or '2013/07/30'
-
Date literal: DATE'2013-07-30' or DATE'2013/07/30'
-
(b) Predefined output representation
When date data is retrieved using the adbsql command (or similar commands), the results are output in a format that follows the predefined output representation.
- Format of the predefined output representation:
-
'YYYY-MM-DD'
The year is expressed in four digits (YYYY), and the month (MM) and day (DD) in two digits. The fields are padded on the left with zeros, as necessary.
- Example:
-
For the date data X'20130730', the predefined output representation is as follows.
'2013-07-30'
(2) Predefined character-string representation of times
Predefined character-string representations of times include a predefined input representation and a predefined output representation.
(a) Predefined input representation
A character string literal that follows the format of the predefined input representation for times can be used as a literal representing a time. The format of the predefined input representation of a time is as follows.
- Format of the predefined input representation:
-
'hh:mm:ss.nn...n'
-
The hour (hh), minutes (mm), and seconds (ss) are expressed in two digits. Pad the fields with zeros on the left, as necessary.
-
To use fractional seconds, add them in the .nn...n format. nn...n is represented by 3, 6, 9, or 12 digits. If the nn...n portion is not specified with 3, 6, 9, or 12 digits, the fractional seconds precision is assumed as described later. In that case, zero padding is applied to the missing digits on the right.
Number of digits in nn...n
Assumed fractional seconds precision
1, 2
3
4, 5
6
7, 8
9
10, 11
12
-
An error results if nn...n contains 13 or more digits.
-
A period is required between the seconds and the fractional seconds.
-
There is no need to specify .nn...n unless you want to use fractional seconds.
-
If you omit nn...n and specify only a period, the data is treated as having a fractional seconds precision of 0.
-
Specify values for hh, mm, ss, and nn...n that are valid for the TIME type (for example, hh must be 00 to 23).
-
- Example:
-
The following representations express the time that is 3 minutes and 58.123456 seconds after the hour of 11 o'clock.
-
Character string literal (predefined input representation): '11:03:58.123456'
-
Time literal: TIME'11:03:58.123456'
-
(b) Predefined output representation
When time data is retrieved using the adbsql command (or a similar command), the results are output in a format that follows the predefined output representation.
- Format of the predefined output representation:
-
'hh:mm:ss.nn...n'
-
The hour (hh), minutes (mm), and seconds (ss) are expressed in two digits. The fields are padded on the left with zeros, as necessary.
-
The fractional seconds are displayed in .nn...n. The number of digits in the fractional seconds depends on the specification of the fractional seconds precision in the time data.
-
If the fractional seconds precision is 0, the .nn...n part is not displayed.
-
- Example:
-
If the time data is X'110358123', the predefined output representation is as follows.
'11:03:58.123'
(3) Predefined character-string representation of time stamps
The predefined character-string representation of time stamps include a predefined input representation and a predefined output representation.
(a) Predefined input representation
A character string literal that follows the format of the predefined input representation for time stamps can be used as a literal representing a time stamp. The format of the predefined input representation of a time stamp is as follows.
- Format of the predefined input representation:
-
'YYYY-MM-DD hh:mm:ss.nn...n' or 'YYYY/MM/DD hh:mm:ss.nn...n'
-
The year is expressed using four digits (YYYY), and the month (MM), day (DD), hour (hh), minutes (mm), and seconds (ss) using two digits. Pad the fields with zeros on the left, as necessary.
-
A space is required between YYYY-MM-DD and hh:mm:ss.
-
Specify .nn...n if you want to use fractional seconds. nn...n represents 3, 6, 9, or 12 digits. The table below shows the fractional seconds precision that is assumed in cases where the number of digits in nn...n is not 3, 6, 9, or 12. In that case, zero padding is applied to the missing digits on the right.
Number of digits in nn...n
Assumed fractional seconds precision
1, 2
3
4, 5
6
7, 8
9
10, 11
12
-
An error results if nn...n contains 13 or more digits.
-
A period is required between the seconds and the fractional seconds.
-
There is no need to specify .nn...n unless you want to use fractional seconds.
-
If you omit nn...n and specify only a period, the data is treated as having a fractional seconds precision of 0.
-
Specify values for YYYY, MM, DD, hh, mm, ss, and nn...n that are valid for the TIMESTAMP type (for example, hh must be 00 to 23).
-
- Example
-
July 30, 2013 at 11:03:58.123456 is expressed as follows.
-
Character string literal (predefined input representation): '2013-07-30 11:03:58.123456' or '2013/07/30 11:03:58.123456'
-
Time stamp literal: TIMESTAMP'2013-07-30 11:03:58.123456' or TIMESTAMP'2013/07/30 11:03:58.123456'
-
(b) Predefined output representation
When time stamp data is retrieved using the adbsql command (or similar commands), the results are output in a format that follows the predefined output representation.
- Format of the predefined output representation:
-
'YYYY-MM-DD hh:mm:ss.nn...n'
-
The year is expressed using four digits (YYYY), and the month (MM), day (DD), hour (hh), minutes (mm), and seconds (ss) using two digits. The fields are padded with zeros on the left, as necessary.
-
The fractional seconds are displayed in .nn...n. The number of digits in the fractional seconds depends on the specification of the fractional seconds precision in the time stamp data.
-
If the fractional seconds precision is 0, the .nn...n part is not displayed.
-
- Example:
-
For the time stamp data X'20130730110358123', the predefined output representation is as follows.
'2013-07-30 11:03:58.123'