Format templates for dates and times

In order to create dates and times from texts, it is necessary to specify a template that follows a specific convention. This is an overview of possible template values. The following values can be used within a template.



Character

Meaning

Type

Example

G

Era designator.

Text.

AD

y

Year.

Year.

2005, 05

M

Month.

Month.

July, Jul, 07

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.g. second Wednesday in February)

E

Day of week.

Text.

Tuesday, Tue

a

Am/pm marker.

Text.

AM, 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 in second.

Number.

978

z

Time zone.

General time zone.

Central European Time, CET, GMT+01:00

Z

Time zone.

RFC 822.

+0100

C

Century (>=0).

Number.

20

Y

Week year (>= 0).

Number.

2008 (for 31.12.2007; counts as day in first calendar week of 2008)

Pattern letters are usually repeated, as their number determines the exact presentation.


  • Text: For formatting, if the number of pattern letters is 4 or more, the full form is used. Otherwise, a short or abbreviated form is used if available. For parsing, both forms are accepted, independent of the number of pattern letters. The presentation depends on the used Locale.

  • Number: For formatting, the number of pattern letters is the minimum number of digits, and shorter numbers are zero-padded to this amount. For parsing, the number of pattern letters is ignored unless it's needed to separate two adjacent fields.

  • Year: For formatting, if the number of pattern letters is 2, the year is truncated to 2 digits. Otherwise, it is interpreted as a number. For parsing, if the number of pattern letters is more than 2, the year is interpreted literally, regardless of the number of digits. So using the pattern MM/dd/yyyy, 01/11/12 parses to Jan 11, 12 A.D. For parsing with the abbreviated year pattern (y or yy), the date is set to be in the past or in the future using a specific logic (up the 80 years in the past, up to 20 years in the future).

  • Month: If the number of pattern letters is 3 or more, the month is interpreted as text. Otherwise, it is interpreted as a number.

  • General time zone: Time zones are interpreted as text if they have names. For time zones representing a GMT offset value, the syntax following the regular expression GMT(\+|\-)\d\d*\:\d\d is used. Hours must be between 0 and 23, and minutes must be between 00 and 59. The format is locale independent and digits must be taken from the Basic Latin block of the Unicode standard. For parsing, RFC 822 time zones are also accepted.

  • RFC 822 time zone: For formatting, the RFC 822 4-digit time zone format following the regular expression (+|-)\d\d\d\d is used. The hours (the first two digits) must be between 00 and 23. Other definitions are as for general time zones. For parsing, general time zones are also accepted.

W3CDTF


The W3CDTF time format is supported in Timestamp fields of the source and target structure, as well as in the "create-date" functions. This format is often used in XML documents. Example: 1997-07-16T19:20:30+01:00

Time zone source structure field


The specification of the time zone in the W3CDTF value is mandatory. The W3CDTF value is converted to the local time zone of the system and the timestamp is then generated from it. The following W3CDTF format errors are corrected.


  • +0100, +1:00, +01, +1 are processed like +01:00. Analogous for a negative shift. The numerical value must not exceed +-24.

  • +01:45 (no full hours) is accepted if there are no other format errors.


If you explicitly expect format errors, use W3CEX instead of W3CDTF as the format template. Then, after correcting the format errors, a new strict check of the correction result is performed.

Time zone target structure field


The values of timestamp fields in the target structure can be formatted as W3CDTF. For this purpose, W3CDTF is used as the format template. The generated W3CDTF value has the local time zone of the system. If you use format template W3CUTC, UTC is used. Other time zones cannot be reached via timestamp fields with template. For this case, use function create W3CDTF from date (a) for Target Timezone (b), for example, but then on a String field without template.