Date Formatting

Date formatting is used convert text to a date and/or a date to text.

There are two main formatters used in Redwood central server:

DateTimeFormatter - used by the DateTimeZone class, Platform Agents, and REL functions
SimpleDateFormat - used by REST, SOAP, and SQL

DateTimeFormatter

The DateTimeZone class uses the DateTimeFormatter for converting a date to text (formatting) and converting text to a date (parsing).

These conversions are based on patterns. uuuu/MM/dd HH:mm:ss,SSS i for example: 2023/07/27 15:45:44,998 Europe/Berlin

DateTimeFormatter	Meaning	Presentation	DateTimeFormatter Examples
G	Era designator	Text	`G` - AD
u	Year	Year	`u`, `uuu`, `uuuu` - 2023 `uu` - 23 `uuuuu` - 02023
y	Year of era	Number	`y`, `yyy`, `yyyy` - 2023 `yy` - 23 `yyyyy` - 02023
D	Day in year	Number	`D`,`DD` - 61 `DDD` - 061
M/L	Month in year	Number/Text	`M` - 3 `MM` - 03 `MMM`, `MMMM` - Mar `MMMMM` - M `L` - 3 `LL` - 03 `LLL`, `LLLL` - Mar `LLLLL` - M
d	Day in month	Number	`d` - 2 `dd` - 02
g	modified-julian-day	Number	`g` - 60005
Q/q	quarter-of-year	number/text	`q` - 1 `qq` - 01 `qqq` - Q1 `qqqq` - 1st quarter `qqqqq` - 1 `Q` - 1 `QQ` - 01 `QQQ` - Q1 `QQQQ` - 1st quarter `QQQQQ` - 1
Y	week-based-year	year	`Y`, `YYY`, `YYYY` - 2023 `YY` - 23 `YYYYY` - 02023
w	Week in year	Number	`w` - 9 `ww` - 09
W	Week in month	Number	`W` - 1
E	Day in week	Text	`E`, `EE`, `EEE` - Thu `EEEE` - Thursday
e/c	Day number in week (Sunday is 1)	Number	`e` - 5 (Thursday) `c` - 5 (Thursday)
F	Day of week in month	Number	`F` - 2
a	AM/PM marker	Text	`a` - PM
B	period-of-day	Text	`B` - in the afternoon
h	Hour in AM/PM (1-12)	Number	`h` - 3 `hh` - 03
K	Hour in AM/PM (0-11)	Number	`K` - 3 `KK` - 03
k	Hour in day (1-24)	Number	`k` - 15 `kk` - 15
H	Hour in day (0-23)	Number	`H` - 15 `HH` - 15
m	Minute in hour	Number	`m`, `mm` - 45
s	Second in minute	Number	`s`, `ss` - 44
S	Millisecond	Number	`S` - 9 `SS` - 99 `SSS` - 998
A	millisecond in day	Number	`A` - 56744998
n	nano of second	Number	`n` - 998000000
N	nano of day	Number	`N` - 56744998000000
V	time-zone ID	Text	`VV` - Europe/Berlin
v	time-zone ID	General time zone	`v` - CET
z	Time zone	General time zone	`z`, `zz`, `zzz` - CET `zzzz` - Central European Standard Time
O	localized zone offset	offset-O	`O` - GMT+1
X	Time zone	ISO 8601 time zone	`X` - +01 `XX`, `XXX`, `XXXX`, `XXXXX` - +01:00
Z	Time zone	RFC 822 time zone	`Z` - +0100 `ZZ`, `ZZZ` - +0100 `ZZZZ` - GMT+01:00 `ZZZZZ` - +01:00
i	Olson time zone	Text	`i` - Europe/Berlin
'	Quoting	Text, must be duplicated to escape	`''` - ' `'some text'` - some text
`{<locale>}`	Locale to use for the text elements of the date. If multiple locales are specified, the last specified is used.	text	`{de} uuuuMMdd EEEE` - 20230302 Donnerstag `{fr} E` - Jeu

Escaping

To insert text in a date format, you specify '<text>', to insert a ' in the date format, you escape it with an ', so '' will be formatted as '. Note that if you are using REL, you must escape each ' with a \. You escape all literal characters you wish to insert into the date format.

Example

The given date and time are 2023-07-27 12:08:56 local time in the US Pacific Time time zone.

Java SimpleDateFormat	Result
`uuuu.MM.dd G 'at' HH:mm:ss z`	2023.07.04 AD at 12:08:56 PDT
`EEE, MMM d, ''yy`	Thu, Jul 27, '23
`EEE, MMM d, ''uu`	Wed, Jul 4, '23
`EEE, MMM d, ''yy`	Wed, Jul 4, '23
`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 a`	2023.Jul.27 AD 12:08 PM
`{en} EEE, d MMM yyyy HH:mm:ss Z`	Thu, 27 Jul 2023 12:08:56 -0700
`{fr} EEE, d MMM yyyy HH:mm:ss Z`	Thu, 27 Jul 2023 12:08:56 -0700
`yyMMddHHmmssZ`	230727120856-0700
`yyyy-MM-dd'T'HH:mm:ss.SSSZ`	2023-07-27T12:08:56.235-0700
`yyyy-MM-dd'T'HH:mm:ssXXX`	2023-07-27T12:08:56-07:00
`yyyy-'W'ww-e`	2023-W27-3
`MM/dd/yyyy hh:mm:ss VV`	07/04/2023 12:08:56 America/Los Angeles
`=Time.format(Time.now('Europe/Paris'), 'dd/MM/uuuu hh:mm:ss i')`	27/07/2023 21:08:56 Europe/Paris
`=Time.format(Time.now('America/Los Angeles'), 'yyyyMMddHHmm')`	202307271208
`=Time.format(Time.now('America/Los Angeles'), 'yyyy.MM.dd.HH-mm-ss')`	2023.07.27.12-08-56
`=Time.format(Time.now('America/Los Angeles'), 'EEE-dd-MM-yy')`	Thu-27-07-23
`=Time.format(Time.now('America/Los Angeles'), 'hmma')`	1208PM
`=Time.format(Time.now('Europe/Paris'), 'dd MM \'\'yy hh:mm:ss VV')`	27 07 '23 21:08:56 Europe/Paris

Important Changes in 9.2.9 and Later

The Java SimpleDateFormat class was used to format and parse DateTimeZone objects up until version 9.2.8 (inclusive). An example for a dynamic date using Java SimpleDateFormat would be the following:

=Time.format(Time.now('America/Los Angeles'),'yyyyMMdd')

This would be safest to specify as =Time.format(Time.now('America/Los Angeles'),'uuuuMMdd') to correctly account for leap years.

You can also use the Time.format() REL function as follows, note that it also accepts the i to included the time zone:

=Time.format(Time.now('America/Los Angeles'), 'dd/MM/yyyy HH:mm:ss i')

Result:

2023/07/27 15:32:33 Europe/Paris

That would be expressed as the following with DateTimeFormatter

=Time.format(Time.now('America/Los Angeles'), 'dd/MM/uuuu HH:mm:ss i')

Result:

2023/07/27 15:32:33 Europe/Paris

Note: The yyyy does not always account for leap years, for example when STRICT parsing is used. The uuuu syntax is recommended.

The following table lists all valid letters that can be used to form a pattern which represents the date format.

The current date is: 2023/03/02 15:45:44,998 Europe/Berlin (yyyy/MM/dd HH:mm:ss,SSS i)

SimpleDateFormat	DateTimeFormatter	Meaning	Presentation	DateTimeFormatter Examples
	u	Year	Year	`u`, `uuu`, `uuuu` - 2023 `uu` - 23 `uuuuu` - 02023
u	e/c	Day number in week (Sunday is 1)	Number	`e` - 5 (Thursday) `c` - 5 (Thursday)

Important Differences

Superfluous repetitions of letters cause exceptions.
You must escape all literal characters in the format.
u is no longer day number of week but year, use e or c instead.
Use of u instead of y is recommended to cater for leap years.
Start of the week used to depend on the locale with SimepleDateFormat, with DateFormatter, it is always Monday unless you explicitly set it in Java code.
The minimum days in the first week is now 4, there is no minimum in SimpleDateFormat.

{
  String DATE_FORMAT = "EEE, MMM d, hh:mm:ss aa";
  DateTimeZone dtz = new DateTimeZone();
  String creationTime = dtz.toFormattedString(DATE_FORMAT);
  jcsOut.println(creationTime);
}

In version 9.2.8 and lower, the above worked, notice the <code>aa</code> (repetition is superfluous, here, as <code>a</code> would have worked as well). This does not work in 9.2.9 and later.

SimpleDateFormat