The datetime data types are described below. Although datetime values can be used in certain arithmetic and string operations and are compatible with certain strings, they are neither strings nor numbers.
A date is a three-part value (year, month, and day). The range of the year part is 0001 to 9999. The range of the month part is 1 to 12. The range of the day part is 1 to x, where x depends on the month.
The internal representation of a date is a string of 4 bytes. Each byte consists of 2 packed decimal digits. The first 2 bytes represent the year, the third byte the month, and the last byte the day.
The length of a DATE column, as described in the SQLDA, is 10 bytes, which is the appropriate length for a character string representation of the value.
A time is a three-part value (hour, minute, and second) designating a time of day under a 24-hour clock. The range of the hour part is 0 to 24; while the range of the other parts is 0 to 59. If the hour is 24, the minute and second specifications will be zero.
The internal representation of a time is a string of 3 bytes. Each byte is 2 packed decimal digits. The first byte represents the hour, the second byte the minute, and the last byte the second.
The length of a TIME column, as described in the SQLDA, is 8 bytes, which is the appropriate length for a character string representation of the value.
A timestamp is a seven-part value (year, month, day, hour, minute, second, and microsecond) that designates a datetime as defined above, except that the time includes a fractional specification of microseconds.
The internal representation of a timestamp is a string of 10 bytes, each of which consists of 2 packed decimal digits. The first 4 bytes represent the date, the next 3 bytes the time, and the last 3 bytes the microseconds.
The length of a TIMESTAMP column, as described in the SQLDA, is 26 bytes, which is the appropriate length for the character string representation of the value.
Values whose data types are DATE, TIME, or TIMESTAMP are represented in an internal form that is transparent to the SQL user. Dates, times, and timestamps can, however, also be represented by character strings, and these representations directly concern the SQL user since there are no constants or variables whose data types are DATE, TIME, or TIMESTAMP. Thus, to be retrieved, a datetime value must be assigned to a character string variable. The character string representation is normally the default format of datetime values associated with the country code of the database, unless overridden by specification of the F format option when the program is precompiled or bound to the database. See Table 141 for a listing of the string formats for the various country codes.
When a valid string representation of a datetime value is used in an operation with an internal datetime value, the string representation is converted to the internal form of the date, time, or timestamp before the operation is performed. The following sections define the valid string representations of datetime values.
A string representation of a date is a character string that starts with a digit and has a length of at least 8 characters. Trailing blanks may be included; leading zeros may be omitted from the month and day portions.
Valid string formats for dates are listed in Table 1. Each format is
identified by name and includes an associated abbreviation and an example of
its use.
Table 139. Formats for String Representations of Dates
| Format Name | Abbreviation | Date Format | Example |
|---|---|---|---|
| International Standards Organization | ISO | yyyy-mm-dd | 1991-10-27 |
| IBM USA standard | USA | mm/dd/yyyy | 10/27/1991 |
| IBM European standard | EUR | dd.mm.yyyy | 27.10.1991 |
| Japanese Industrial Standard Christian era | JIS | yyyy-mm-dd | 1991-10-27 |
| Site-defined (Local) | LOC | Depends on database country code | -- |
A string representation of a time is a character string that starts with a digit and has a length of at least 4 characters. Trailing blanks may be included; a leading zero may be omitted from the hour part of the time and seconds may be omitted entirely. If you choose to omit seconds, an implicit specification of 0 seconds is assumed. Thus, 13.30 is equivalent to 13.30.00.
Valid string formats for times are listed in Table 140. Each format is identified by name and includes an associated
abbreviation and an example of its use.
Table 140. Formats for String Representations of Times
| Format Name | Abbreviation | Time Format | Example |
|---|---|---|---|
| International Standards Organization | ISO | hh.mm.ss | 13.30.05 |
| IBM USA standard | USA | hh:mm AM or PM | 1:30 PM |
| IBM European standard | EUR | hh.mm.ss | 13.30.05 |
| Japanese Industrial Standard Christian Era | JIS | hh:mm:ss | 13:30:05 |
| Site-defined (Local) | LOC | Depends on application country code | -- |
Notes:
12:01 AM through 12:59 AM corresponds to 00.01.00 through 00.59.00.
01:00 AM through 11:59 AM corresponds to 01.00.00 through 11.59.00.
12:00 PM (noon) through 11:59 PM corresponds to 12.00.00 through 23.59.00.
12:00 AM (midnight) corresponds to 24.00.00 and 00:00 AM (midnight) corresponds to 00.00.00.
A string representation of a timestamp is a character string that starts with a digit and has a length of at least 16 characters. The complete string representation of a timestamp has the form yyyy-mm-dd-hh.mm.ss.nnnnnn. Trailing blanks may be included. Leading zeros may be omitted from the month, day, and hour part of the timestamp, and microseconds may be truncated or entirely omitted. If you choose to omit any digit of the microseconds portion, an implicit specification of 0 is assumed. Thus, 1991-3-2-8.30.00 is equivalent to 1991-03-02-08.30.00.000000.
Date and timestamp strings must contain only single-byte characters and digits.
The character string representation of date and time formats is the default format of datetime values associated with the country code of the application. This default format may be overridden by specification of the F format option when the program is precompiled or bound to the database.
The following is a description of the input and output formats for date and time:
Table 141. Date and Time Formats by Country Code
| Country Code | Local Date Format | Local Time Format | Default Output Date Format | Input Date Formats |
|---|---|---|---|---|
| 785 Arabic | dd/mm/yyyy | JIS | LOC | LOC, EUR, ISO |
| 001 Australia (1) | mm-dd-yyyy | JIS | LOC | LOC, USA, EUR, ISO |
| 061 Australia | dd-mm-yyyy | JIS | LOC | LOC, USA, EUR, ISO |
| 032 Belgium | dd/mm/yyyy | JIS | LOC | LOC, EUR, ISO |
| 055 Brazil | dd.mm.yy | JIS | LOC | LOC, USA, EUR, ISO |
| 359 Bulgaria | dd.mm.yyyy | JIS | EUR | LOC, USA, EUR, ISO |
| 001 Canada | mm-dd-yyyy | JIS | USA | LOC, USA, EUR, ISO |
| 002 Canada (French) | dd-mm-yyyy | ISO | ISO | LOC, USA, EUR, ISO |
| 385 Croatia | yyyy-mm-dd | JIS | ISO | LOC, USA, EUR, ISO |
| 042 Czech Republic | yyyy-mm-dd | JIS | ISO | LOC, USA, EUR, ISO |
| 045 Denmark | dd-mm-yyyy | ISO | ISO | LOC, USA, EUR, ISO |
| 358 Finland | dd/mm/yyyy | ISO | EUR | LOC, EUR, ISO |
| 389 FYR Macedonia | dd.mm.yyyy | JIS | EUR | LOC, USA, EUR, ISO |
| 033 France | dd/mm/yyyy | JIS | EUR | LOC, EUR, ISO |
| 049 Germany | dd/mm/yyyy | ISO | ISO | LOC, EUR, ISO |
| 030 Greece | dd/mm/yyyy | JIS | LOC | LOC, EUR, ISO |
| 036 Hungary | yyyy-mm-dd | JIS | ISO | LOC, USA, EUR, ISO |
| 354 Iceland | dd-mm-yyyy | JIS | LOC | LOC, USA, EUR, ISO |
| 972 Israel | dd/mm/yyyy | JIS | LOC | LOC, EUR, ISO |
| 039 Italy | dd/mm/yyyy | JIS | LOC | LOC, EUR, ISO |
| 081 Japan | mm/dd/yyyy | JIS | ISO | LOC, USA, EUR, ISO |
| 082 Korea | mm/dd/yyyy | JIS | ISO | LOC, USA, EUR, ISO |
| 001 Latin America (1) | mm-dd-yyyy | JIS | LOC | LOC, USA, EUR, ISO |
| 003 Latin America | dd-mm-yyyy | JIS | LOC | LOC, EUR, ISO |
| 031 Netherlands | dd-mm-yyyy | JIS | LOC | LOC, USA, EUR, ISO |
| 047 Norway | dd/mm/yyyy | ISO | EUR | LOC, EUR, ISO |
| 048 Poland | yyyy-mm-dd | JIS | ISO | LOC, USA, EUR, ISO |
| 351 Portugal | dd/mm/yyyy | JIS | LOC | LOC, EUR, ISO |
| 086 PRC | mm/dd/yyyy | JIS | ISO | LOC, USA, EUR, ISO |
| 040 Romania | yyyy-mm-dd | JIS | ISO | LOC, USA, EUR, ISO |
| 007 Russia | dd/mm/yyyy | ISO | LOC | LOC, EUR, ISO |
| 381 Serbia/Montenegro | yyyy-mm-dd | JIS | ISO | LOC, USA, EUR, ISO |
| 042 Slovakia | yyyy-mm-dd | JIS | ISO | LOC, USA, EUR, ISO |
| 386 Slovenia | yyyy-mm-dd | JIS | ISO | LOC, USA, EUR, ISO |
| 034 Spain | dd/mm/yyyy | JIS | LOC | LOC, EUR, ISO |
| 046 Sweden | dd/mm/yyyy | ISO | ISO | LOC, EUR, ISO |
| 041 Switzerland | dd/mm/yyyy | ISO | EUR | LOC, EUR, ISO |
| 088 Taiwan | mm-dd-yyyy | JIS | ISO | LOC, USA, EUR, ISO |
| 066 Thailand (2) | dd/mm/yyyy | JIS | LOC | LOC, EUR, ISO |
| 090 Turkey | dd/mm/yyyy | JIS | LOC | LOC, EUR, ISO |
| 044 UK | dd/mm/yyyy | JIS | LOC | LOC, EUR, ISO |
| 001 USA | mm-dd-yyyy | JIS | USA | LOC, USA, EUR, ISO |
|
Notes:
| ||||