Data and Time Format
A formatting string describes how a date/time values should be read and written from(to) string representation (flat files, human readable output, etc.).
A format can also specify an engine which CloudConnect will use by specifying a prefix (see below). There are two built-in date engines available: standard Java and third-party Joda (http://joda-time.sourceforge.net).
Table 28.2. Available date engines
| Date engine | Prefix | Default | Description | Example |
|---|---|---|---|---|
| Java | java: | yes - when no prefix is given | Standard Java date implementation. Provides lenient, error-prone and full-featured parsing and writing. It has moderate speed and is generally a good choice unless you need to work with large quantities of date/time fields. For advanced study please refer to Java SimpleDateFormat documentation. | java:yyyy-MM-dd HH:mm:ss |
| Joda | joda: | An improved third-party date library. Joda is more strict on input data accuracy when parsing and does not work well with time zones. It does, however, provide a 20-30% speed increase compared to standard Java. For further reading please visit the project site at http://joda-time.sourceforge.net). Joda may be convenient for AS/400 machines. On the other hand, Joda is unable to read time zone
expressed with any number of | joda:yyyy-MM-dd HH:mm:ss |
Please note, that actual format strings for Java and Joda are almost 100% compatible with each other - see tables below.
![[Important]](figures/important.png) | Important |
|---|---|
The format patterns described in this section are used both in metadata as the Format property and in CTL. |
At first, we provide the list of pattern syntax, the rules and the examples of its usage for Java:
Table 28.3. Date Format Pattern Syntax (Java)
| Letter | Date or Time Component | Presentation | Examples |
|---|---|---|---|
| G | Era designator | Text | AD |
| Y | Year | Year | 1996; 96 |
| M | Month in year | Month | July; Jul; VII; 07; 7 |
| w | Week in year | Number | 27 |
| W | Week in month | Number | 2 |
| D | Day in year | Number | 189 |
| d | Day in month | Number | 10 |
| F | Day of week in month | Number | 2 |
| E | Day in week | Text | Tuesday; Tue |
| a | Am/pm marker | Text | PM |
| H | Hour in day (0-23) | Number | 0 |
| k | Hour in day (1-24) | Number | 24 |
| K | Hour in am/pm (0-11) | Number | 0 |
| h | Hour in am/pm (1-12) | Number | 12 |
| m | Minute in hour | Number | 30 |
| s | Second in minute | Number | 55 |
| S | Millisecond | Number | 970 |
| z | Time zone | General time zone | Pacific Standard Time; PST; GMT-08:00 |
| Z | Time zone | RFC 822 time zone | -0800 |
| ' | Escape for text/id | Delimiter | (none) |
| '' | Single quote | Literal | ' |
The number of symbol letters you specify also determines the format. For example, if the "zz" pattern results in "PDT", then the "zzzz" pattern generates "Pacific Daylight Time". The following table summarizes these rules:
Table 28.4. Rules for Date Format Usage (Java)
| Presentation | Processing | Number of Pattern Letters | Form |
|---|---|---|---|
| Text | Formatting | 1 - 3 | short or abbreviated form, if one exists |
| Text | Formatting | >= 4 | full form |
| Text | Parsing | >= 1 | both forms |
| Year | Formatting | 2 | truncated to 2 digits |
| Year | Formatting | 1 or >= 3 | interpreted as Number. |
| Year | Parsing | 1 | intepreted literally |
| Year | Parsing | 2 | interpreted relative to the century within 80 years
before or 20 years after the time when the
SimpleDateFormat instance is
created |
| Year | Parsing | >= 3 | intepreted literally |
| Month | Both | 1-2 | interpreted as a Number |
| Month | Parsing | >= 3 | interpreted as Text (using Roman numbers, abbreviated month name - if exists, or full month name) |
| Month | Formatting | 3 | interpreted as Text (using Roman numbers, or abbreviated month name - if exists) |
| Month | Formatting | >= 4 | interpreted as Text (full month name) |
| Number | Formatting | minimum number of required digits | shorter numbers are padded with zeros |
| Number | Parsing | number of pattern letters is ignored (unless needed to separate two adjacent fields) | any form |
| General time zone | Both | 1-3 | short or abbreviated form, if has a name. Otherwise, GMT offset value (GMT[sign][[0]0-23]:[00-59]) |
| General time zone | Both | >= 4 | full form, , if has a name. Otherwise, GMT offset value (GMT[sign][[0]0-23]:[00-59]) |
| General time zone | Parsing | >= 1 | RFC 822 time zone form is allowed |
| RFC 822 time zone | Both | >= 1 | RFC 822 4-digit time zone format is used ([sign][0-23][00-59]) |
| RFC 822 time zone | Parsing | >= 1 | General time zone form is allowed |
Examples of date format patterns and resulting dates follow:
Table 28.5. Date and Time Format Patterns and Results (Java)
| Date and Time Pattern | Result |
|---|---|
| "yyyy.MM.dd G 'at' HH:mm:ss z" | 2001.07.04 AD at 12:08:56 PDT |
| "EEE, MMM d, ''yy" | Wed, Jul 4, '01 |
| "h:mm a" | 12:08 PM |
| "hh 'o''clock' a, zzzz" | 12 o'clock PM, Pacific Daylight Time |
| "K:mm a, z" | 0:08 PM, PDT |
| "yyyyy.MMMMM.dd GGG hh:mm aaa" | 02001.July.04 AD 12:08 PM |
| "EEE, d MMM yyyy HH:mm:ss Z" | Wed, 4 Jul 2001 12:08:56 -0700 |
| "yyMMddHHmmssZ" | 010704120856-0700 |
| "yyyy-MM-dd'T'HH:mm:ss.SSSZ" | 2001-07-04T12:08:56.235-0700 |
The described format patterns are used both in metadata as the Format property and in CTL.
Now the list of format pattern syntax for Joda follows:
Table 28.6. Date Format Pattern Syntax (Joda)
| Symbol | Meaning | Presentation | Examples |
|---|---|---|---|
| G | Era designator | Text | AD |
| C | Century of era (>=0) | Number | 20 |
| Y | Year of era (>=0) | Year | 1996 |
| y | Year | Year | 1996 |
| x | Week of weekyear | Year | 1996 |
| M | Month of year | Month | July; Jul; 07 |
| w | Week of year | Number | 27 |
| D | Day of year | Number | 189 |
| d | Day of month | Number | 10 |
| e | Day of week | Number | 2 |
| E | Day of week | Text | Tuesday; Tue |
| a | Halfday of day | Text | PM |
| H | Hour of day (0-23) | Number | 0 |
| k | Clockhour of day (1-24) | Number | 24 |
| K | Hour of halfday (0-11) | Number | 0 |
| h | Clockhour of halfday (1-12) | Number | 12 |
| m | Minute of hour | Number | 30 |
| s | Second of minute | Number | 55 |
| S | Fraction of second | Number | 970 |
| z | Time zone | Text | Pacific Standard Time; PST |
| Z | Time zone offset/id | Zone | -0800; -08:00; America/Los_Angeles |
| ' | Escape for text/id | Delimiter | (none) |
| '' | Single quote | Literal | ' |
The number of symbol letters you specify also determines the format. The following table summarizes these rules:
Table 28.7. Rules for Date Format Usage (Joda)
| Presentation | Processing | Number of Pattern Letters | Form |
|---|---|---|---|
| Text | Formatting | 1 - 3 | short or abbreviated form, if one exists |
| Text | Formatting | >= 4 | full form |
| Text | Parsing | >= 1 | both forms |
| Year | Formatting | 2 | truncated to 2 digits |
| Year | Formatting | 1 or >= 3 | interpreted as Number. |
| Year | Parsing | >= 1 | intepreted literally |
| Month | Both | 1-2 | interpreted as a Number |
| Month | Parsing | >= 3 | interpreted as Text (using Roman numbers, abbreviated month name - if exists, or full month name) |
| Month | Formatting | 3 | interpreted as Text (using Roman numbers, or abbreviated month name - if exists) |
| Month | Formatting | >= 4 | interpreted as Text (full month name) |
| Number | Formatting | minimum number of required digits | shorter numbers are padded with zeros |
| Number | Parsing | >= 1 | any form |
| Zone name | Formatting | 1-3 | short or abbreviated form |
| Zone name | Formatting | >= 4 | full form |
| Time zone offset/id | Formatting | 1 | Offset without a colon between hours and minutes |
| Time zone offset/id | Formatting | 2 | Offset with a colon between hours and minutes |
| Time zone offset/id | Formatting | >= 3 | Full textual form like this: "Continent/City" |
| Time zone offset/id | Parsing | 1 | Offset without a colon between hours and minutes |
| Time zone offset/id | Parsing | 2 | Offset with a colon between hours and minutes |
| Important |
|---|---|
Remember that parsing with any number of "z" letters is not allowed. And neither parsing with the number of "Z" letters greater than or equal to 3 is allowed. |
See information about data types in metadata and CTL1 and CTL2:
For CTL1:
For CTL2:
They are also used in CTL1 and CTL2 functions. See: