This topic explains how to 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.
When a string is parsed (for example, converting it 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.
The expression pattern is defined by:
The following table lists the characters that you can use in a pattern for formatting or parsing strings in relation to a dateTime. Some notes follow the table; these 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 (as above, 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 |
k | 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 | seconds | Number | 5 |
ss | seconds | Number | 05 |
S | decisecond5 | Number | 7 |
SS | centisecond5 | Number | 70 |
SSS | millisecond5 | Number | 700 |
SSSS | 1/10,000 th seconds5 | Number | 7000 |
SSSSS | 1/100,000 th seconds5 | Number | 70000 |
SSSSSS | 1/1,000,000 th seconds5 | Number | 700000 |
T | ISO8601 Time (up to HH:mm:ss.SSSZZZ)4 | text | 12:06:56.568+01:00 |
TU | ISO8601 Time (as above, 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) | Text | gmt |
zzzz | time zone (full name) | Text | Greenwich Mean 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 quotes) single quote within escaped text | 'o''clock' |
The presentation of the dateTime object depends on the symbols that you specify.
If you use the lower case y symbol, the adjustment is not done and unpredictable results might occur for dates around year end. For example, if the string 2002 01 Monday is formatted:
Use Y only together with w. If you specify Y without w, the year is ignored. For example, if you specify YYYY-MM-dd to format 1996-03-01 the result is 2006-03-01 because the year input is ignored and the current year is assumed.
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.
Use these symbols only on their own.
The following table shows how the output form relates to the logical data type.
Logical MRM data type | Logical 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 |
An element or attribute of logical type xsd:dateTime or xsd:time that contains a dateTime as a string can specify Consolidated Universal Time (UTC) by using either the Z character 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 should appear by using the dateTime format property of the element or attribute. Alternatively, you can preserve the input UTC format by checking message set property Use input UTC format on output. If this property is checked, the UTC format is preserved in the output message and overrides the format that is implied by the dateTime format property.
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 |