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 enginePrefixDefaultDescriptionExample
Javajava: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
Jodajoda: 

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 z letters and/or at least three Z letters in a pattern.

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]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)

LetterDate or Time ComponentPresentationExamples
GEra designatorTextAD
YYearYear1996; 96
MMonth in yearMonthJuly; Jul; VII; 07; 7
wWeek in yearNumber27
WWeek in monthNumber2
DDay in yearNumber189
dDay in monthNumber10
FDay of week in monthNumber2
EDay in weekTextTuesday; Tue
aAm/pm markerTextPM
HHour in day (0-23)Number0
kHour in day (1-24)Number24
KHour in am/pm (0-11)Number0
hHour in am/pm (1-12)Number12
mMinute in hourNumber30
sSecond in minuteNumber55
SMillisecondNumber970
zTime zoneGeneral time zonePacific Standard Time; PST; GMT-08:00
ZTime zoneRFC 822 time zone-0800
'Escape for text/idDelimiter(none)
''Single quoteLiteral'

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)

PresentationProcessingNumber of Pattern LettersForm
TextFormatting1 - 3short or abbreviated form, if one exists
TextFormatting>= 4full form
TextParsing>= 1both forms
YearFormatting2truncated to 2 digits
YearFormatting1 or >= 3interpreted as Number.
YearParsing1intepreted literally
YearParsing2interpreted relative to the century within 80 years before or 20 years after the time when the SimpleDateFormat instance is created
YearParsing>= 3intepreted literally
MonthBoth1-2interpreted as a Number
MonthParsing>= 3interpreted as Text (using Roman numbers, abbreviated month name - if exists, or full month name)
MonthFormatting3interpreted as Text (using Roman numbers, or abbreviated month name - if exists)
MonthFormatting>= 4interpreted as Text (full month name)
NumberFormattingminimum number of required digitsshorter numbers are padded with zeros
NumberParsingnumber of pattern letters is ignored (unless needed to separate two adjacent fields)any form
General time zoneBoth1-3short or abbreviated form, if has a name. Otherwise, GMT offset value (GMT[sign][[0]0-23]:[00-59])
General time zoneBoth>= 4full form, , if has a name. Otherwise, GMT offset value (GMT[sign][[0]0-23]:[00-59])
General time zoneParsing>= 1RFC 822 time zone form is allowed
RFC 822 time zoneBoth>= 1RFC 822 4-digit time zone format is used ([sign][0-23][00-59])
RFC 822 time zoneParsing>= 1General 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 PatternResult
"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)

SymbolMeaningPresentationExamples
GEra designatorTextAD
CCentury of era (>=0)Number20
YYear of era (>=0)Year1996
yYearYear1996
xWeek of weekyearYear1996
MMonth of yearMonthJuly; Jul; 07
wWeek of yearNumber27
DDay of yearNumber189
dDay of monthNumber10
eDay of weekNumber2
EDay of weekTextTuesday; Tue
aHalfday of dayTextPM
HHour of day (0-23)Number0
kClockhour of day (1-24)Number24
KHour of halfday (0-11)Number0
hClockhour of halfday (1-12)Number12
mMinute of hourNumber30
sSecond of minuteNumber55
SFraction of secondNumber970
zTime zoneTextPacific Standard Time; PST
ZTime zone offset/idZone-0800; -08:00; America/Los_Angeles
'Escape for text/idDelimiter(none)
''Single quoteLiteral'

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)

PresentationProcessingNumber of Pattern LettersForm
TextFormatting1 - 3short or abbreviated form, if one exists
TextFormatting>= 4full form
TextParsing>= 1both forms
YearFormatting2truncated to 2 digits
YearFormatting1 or >= 3interpreted as Number.
YearParsing>= 1intepreted literally
MonthBoth1-2interpreted as a Number
MonthParsing>= 3interpreted as Text (using Roman numbers, abbreviated month name - if exists, or full month name)
MonthFormatting3interpreted as Text (using Roman numbers, or abbreviated month name - if exists)
MonthFormatting>= 4interpreted as Text (full month name)
NumberFormattingminimum number of required digitsshorter numbers are padded with zeros
NumberParsing>= 1any form
Zone nameFormatting1-3short or abbreviated form
Zone nameFormatting>= 4full form
Time zone offset/idFormatting1Offset without a colon between hours and minutes
Time zone offset/idFormatting2Offset with a colon between hours and minutes
Time zone offset/idFormatting>= 3Full textual form like this: "Continent/City"
Time zone offset/idParsing1Offset without a colon between hours and minutes
Time zone offset/idParsing2Offset with a colon between hours and minutes

[Important]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:

They are also used in CTL1 and CTL2 functions. See:

For CTL1:

For CTL2: