Formatting and parsing dateTimes as strings
This section gives information on how you can specify the dateTime format using a string of pattern letters.
When you convert a date or time into a string, a format pattern must be applied that directs the conversion. Apply the format pattern to convert a date or time into a string, or to parse a string into a date or time.
During the conversion (for example, of a dateTime into a string), a pattern or a set of tokens is replaced with the equivalent source. The following diagram shows how a pattern is used to format a dateTime source to produce a character string output.
![This diagram shows the output string that results from a dateTime source and a format pattern.](ad09290_fig1.gif)
When a string is parsed (for example, when converting the string to a dateTime), the pattern or set of tokens is used to determine which part of the target dateTime is represented by which part of the string. The following diagram shows how this is done.
![This diagram shows the dateTime output that results from a string data source and a format pattern.](ad09290_fig2.gif)
Syntax
The expression pattern is defined by:
Characters for formatting a dateTime as a string
The following table lists the characters that you can use in a pattern for formatting or parsing strings in relation to a dateTime. The table is followed by some notes that explain more about some of the examples in the table.
Symbol | Meaning | Presentation | Examples |
---|---|---|---|
a | am or pm marker | Text | Input am, AM, pm, PM. Output AM or PM |
d | day in month (1-31) | Number | 1, 20 |
dd | day in month (01-31) | Number | 01, 31 |
D | day in year (1-366) | Number | 3, 80, 100 |
DD | day in year (01-366) | Number | 03, 80, 366 |
DDD | day in year (001-366) | Number | 003 |
e | day in week (1-7)1 | Number | 2 |
EEE | day in week1 | Text | Tue |
EEEE | day in week1 | Text | Tuesday |
F | day of week in month (1-5)2 | Number | 2 |
G | Era | Text | BC or AD |
h | hour in am or pm (1-12) | Number | 6 |
hh | hour in am or pm (01-12) | Number | 06 |
H | hour of day in 24 hour form (0-23)3 | Number | 7 |
HH | hour of day in 24 hour form (00-23)3 | Number | 07 |
I | ISO8601 Date/Time (up to yyyy-MM-dd'T'HH:mm:ss. SSSZZZ)4 | Text | 2006-10-07T12:06:56.568+01:00 |
IU | ISO8601 Date/Time (similar to I, but ZZZ with output "Z" if the time zone is +00:00)4 | Text | 2006-10-07T12:06:56.568+01:00, 2003-12 -15T15:42:12.000Z |
k | hour of day in 24 hour form (1-24)3 | Number | 8 |
kk | hour of day in 24 hour form (01-24)3 | Number | 08 |
K | hour in am or pm (0-11) | Number | 9 |
KK | hour in am or pm (00-11) | Number | 09 |
m | minute | Number | 4 |
mm | minute | Number | 04 |
M | numeric month | Number | 5, 12 |
MM | numeric month | Number | 05, 12 |
MMM | named month | Text | Jan, Feb |
MMMM | named month | Text | January, February |
s | seconds10 | Number | 5 |
ss | seconds10 | Number | 05 |
S | decisecond5 | Number | 7 |
SS | centisecond5 | Number | 70 |
SSS | millisecond5 | Number | 700 |
SSSS | 0.0001 second5 | Number | 7000 |
SSSSS | 0.00001 second5 | Number | 70000 |
SSSSSS | 0.000001 second5 | Number | 700000 |
T | ISO8601 Time (up to HH:mm:ss.SSSZZZ)4 | Text | 12:06:56.568+01:00 |
TU | ISO8601 Time (similar to T, but a time zone of +00:00 is replaced with 'Z')4 | Text | 12:06:56.568+01:00, 15:42:12.000Z |
w | week in year6 | Number | 7, 53 |
ww | week in year6 | Number | 07, 53 |
W | week in month7 | Number | 2 |
yy | year8 | Number | 06 |
yyyy | year8 | Number | 2006 |
YY | year: use with week in year only6 | Number | 06 |
YYYY | year: use with week in year only6 | Number | 2006 |
zzz | time zone (abbreviated name)9 | Text | EST |
zzzz | time zone (full name) | Text | Eastern Standard Time |
Z | time zone (+/-n) | Text | +3 |
ZZ | time zone (+/-nn) | Text | +03 |
ZZZ | time zone (+/-nn:nn) | Text | +03:00 |
ZZZU | time zone (as ZZZ, "+00:00" is replaced by "Z") | Text | +03:00, Z |
ZZZZ | time zone (GMT+/-nn:nn) | Text | GMT+03:00 |
ZZZZZ | time zone (as ZZZ, but no colon) (+/-nnnn) | Text | +0300 |
' | escape for text | 'User text' | |
" | (two single quotation marks) single quotation mark within escaped text | 'o"clock' |
The presentation of the dateTime object depends on what symbols you specify.
- Text. If you specify four or more of the symbols, the full
form is presented. If you specify less than four symbols, the short
or abbreviated form, if it exists, is presented. For example,
EEEE
producesMonday
,EEE
producesMon
. - Number. The number of characters for a numeric dateTime
component must be within the bounds of the corresponding formatting
symbols. Repeat the symbol to specify the minimum number of digits
that are required. The maximum number of digits allowed is the upper
bound for a particular symbol. For example,
day in month
has an upper bound of 31; therefore, a format string ofd
allows the values 2 or 21 to be parsed but disallows the values 32 and 210. On output, numbers are padded with zeros to the specified length. A year is a special case; see note 8 in the following list. Fractional seconds are also special case; see note 5 in the following list. - Any characters in the pattern that are not in the ranges of ['a'..'z'] and ['A'..'Z'] are treated as quoted text. For example, characters like colon (:), comma (,), period (.), the number sign (hash or pound, #), the at sign (@), and space appear in the resulting time text even if they are not enclosed within single quotes.
- You can create formatting strings that produce unpredictable results;
therefore, you must use these symbols with care. For example, if you
specify
dMyyyy
, you cannot distinguish between day, month, and year.dMyyyy
tells the integration node that a minimum of one character represents the day, a minimum of one character represents the month, and four characters represent the year. Therefore,3111999
can be interpreted as either 3/11/1999 or 31/1/1999.
- You can specify the following values in the
day in week
field:- 1 - Sunday
- 2 - Monday
- 3 - Tuesday
- 4 - Wednesday
- 5 - Thursday
- 6 - Friday
- 7 - Saturday
- 12th July 2006 is the second Wednesday in July and
can be expressed as
2006 July Wednesday 2
using the format stringyyyy MMMM EEEE F
. Note that this format does not represent the Wednesday in week 2 of July 2006, which is 5th July 2006; the format string for this isyyyy MMMM EEEE W
. - 24-hour fields might result in an ambiguous time, if specified with a conflicting am/pm field.
- See ISO8601, I and T DateTime tokens.
- Fractional seconds are represented by uppercase S.
The length must implicitly match the number of format symbols on input.
The format string
ss SSS
orss.SSS
, for example, represents seconds and milliseconds. However, the format stringss.sss
represents a repeated field (of seconds); the value after the period (.) is taken as a seconds field, not as fractional seconds. The output is truncated to the specified length. - In ESQL, the first day of the year is assumed to be in the first
week; therefore, January 1 is always in week 1. As a result, dates
that are specified relative to one year might be in a different year.
For example, "Monday week 1 2005" parsed using "
EEEE' week 'w' 'YYYY
" gives a date of 2004-12-27, because the Monday of the first week in 2005 is a date in 2004.If you use the
y
symbol, the adjustment is not done and unpredictable results might occur for dates around the end of the year. For example, if the string "2005 01 Monday" is formatted:- Monday of week 1 in 2005 using format string "
YYYY ww EEEE
" is correctly interpreted as 27th December 2004 - Monday of week 1 in 2005 using format string "
yyyy ww EEEE
" is incorrectly interpreted as 27th December 2005
- Monday of week 1 in 2005 using format string "
- The first and last week in a month might include days
from neighboring months. For example, Monday 31st July 2006 can be
expressed as Monday in week one of August 2006, which is
2006 08 1 Monday
using format stringyyyy MM W EEEE
. - Year is handled as a special case.
- On output, if the count of
y
is2
, the year is truncated to 2 digits. For example, ifyyyy
produces1997
,yy
produces97
. - On input, for 2 digit years the century window is fixed to 53. For example, an input date of 52 results in a year value of 2052, whereas an input date of 53 gives an output year of 1953, and 97 gives 1997.
- On output, if the count of
- Using the
zzz
option can have ambiguous results. For example,BST
can be interpreted asBangladesh Standard Time
orBritish Summer Time
. For compatibility reasons, IBM® Integration Bus uses the former interpretation.To avoid these problems, use the
zzzz
option with a well-defined name; for example, Europe/London, Asia/Dhaka, or America/Los_Angeles. - Seconds
s
&ss
, must be in the range 0-59. If you need to construct a TIMESTAMP representing the time during a leap second, where the value being created or cast uses the value 60 for seconds, you must handle this case within your ESQL code. The CURRENT_ datetime functions (for example, CURRENT_TIME) within the product never produce a time where the seconds value falls outside of the 0-59 range.
ISO8601, I and T DateTime tokens
If your dateTime values comply with the ISO8601:2000 'Representation of dates and times' standard, consider using the formatting symbols I and T, which match the following subset of the ISO8601 standard.
- The restricted profile as proposed by the W3C at http://www.w3.org/TR/NOTE-datetime
- Truncated representations of calendar dates, as specified in section
5.2.1.3 of ISO8601:2000
- Basic format (subsections c, e, and f)
- Extended format (subsections a, b, and d)
Use the formatting symbols I and T only on their own:
- The I formatting symbol matches any dateTime string that conforms to the supported subset.
- The T formatting symbol matches any dateTime string that conforms to the supported subset that consists of a time portion only.
The following table shows how the output form relates to the logical data type.
Logical model data type | ESQL data type | Output form |
---|---|---|
xsd:dateTime | TIMESTAMP or GMTTIMESTAMP | yyyy-MM-dd'T'HH:mm:ss.SSSZZZ |
xsd:date | DATE | yyyy-MM-dd |
xsd:gYear | INTERVAL | yyyy |
xsd:gYearMonth | INTERVAL | yyyy-MM |
xsd:gMonth | INTERVAL | --MM |
xsd:gmonthDay | INTERVAL | --MM-dd |
xsd:gDay | INTERVAL | ---dd |
xsd:time | TIME / GMTTIME | 'T'HH:mm:ss.SSSZZZ |
- On input, both I and T accept both '+00:00' and 'Z' to indicate a zero time difference from Coordinated Universal Time (UTC), but on output they always generate '+00:00'. If you want 'Z' to always be generated on output, use the IU or TU formatting symbols instead.
- ZZZ always writes '+00:00' to indicate a zero time difference from Coordinated Universal Time (UTC). If you want 'Z' to always be generated on output, use ZZZU instead.
Using the input UTC format on output
An element or attribute of logical type xsd:dateTime or xsd:time that contains a dateTime as a string can specify Coordinated Universal Time (UTC) by using either the Z symbol or time zone +00:00. On input, the MRM parser remembers the UTC format of such elements and attributes. On output, you can specify whether Z or +00:00 is displayed by using the Default DateTime Format property of the element or attribute. Alternatively, you can preserve the input UTC format by selecting the message set property Use input UTC format on output. If this property is selected, the UTC format is preserved in the output message and overrides the format that is implied by the dateTime format property.
Understanding daylight saving time and the CAST function
When the integration node is running in a time zone other than GMT, it calculates the daylight saving time (DST) offset on times that are supplied to it by the CAST function. For CAST to calculate the offset correctly, the time passed into CAST must have a time zone associated with it, as a Z parameter. If no time zone is associated with the value passed, the time is converted into GMT time; it is not treated as a local time stamp.
Also, when you use CAST to cast a string to a time value, the DST offset is calculated using the current system date. To cast a string to a time variable and calculate DST for a specific date, you must also specify the date.
DECLARE castTime TIME;
SET castTime = CAST (timeValue AS TIME FORMAT timePattern)
The
time is not converted into GMT again if the castTime variable is used
in any subsequent code, for example CAST(castDate, castTime AS GMTTIMESTAMP);
Examples
The following table shows a few examples of dateTime formats.
Format pattern | Result |
---|---|
"yyyy.MM.dd 'at' HH:mm:ss ZZZ" | 2006.07.10 at 15:08:56 -05:00 |
"EEE, MMM d, "yy" | Wed, July 10, '06 |
"h:mm a" | 8:08 PM |
"hh o"clock a, ZZZZ" | 09 o'clock AM, GMT+09:00 |
"K:mm a, ZZZ" | 9:34 AM, -05:00 |
"yyyy.MMMMM.dd hh:mm aaa" | 1996.July.10 12:08 PM |
Use within an MRM domain
In MRM it is possible to define an element that has the logical type of dateTime.
When a dateTime element is parsed, a field is created in the message tree that has the ESQL datatype of CURRENT_TIME or CURRENT_TIMESTAMP. However, the CURRENT_TIME and CURRENT_TIMESTAMP data types do not have the functionality to store timezone information, and the MRM does not adjust the time according to the input timezone and the timezone of the integration node.
Although the CURRENT_TIME and CURRENT_TIMESTAMP data types cannot store timezone information, the MRM stores this information as part of the underlying field. This means that if the field is copied between message trees, the timezone information is copied with it, allowing this information to be preserved on output.
Note that the information is preserved only if the field is copied to a field of the same name.
However, if any new field is derived from the original field, the new field does not have the timezone information. This means that if such a field is cast as a character, the new field assumes the timezone of the integration node, but its value is not adjusted for any difference between the input timezone and the timezone of the integration node.
For example,
an input dateTime element containing 2009-02-20T06:08:07-08:00
could
be copied from the input message tree to the output message tree and
appear in an output message in exactly the same format. However, if
the element is cast as character, using format IU
,
by an integration node running GMT the result would be 2009-02-20T06:08:07.000Z
.