Specifying Date Formats

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/11/16 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-11-16 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, Nov 16, '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.Nov.16 AD 12:08 PM
{en} EEE, d MMM yyyy HH:mm:ss Z	Thu, 16 Nov 2023 12:08:56 -0700
{fr} EEE, d MMM yyyy HH:mm:ss Z	jeu., 16 nov. 2023 12:08:56 -0700
yyMMddHHmmssZ	231116120856-0700
yyyy-MM-dd'T'HH:mm:ss.SSSZ	2023-11-16T12:08:56.235-0700
yyyy-MM-dd'T'HH:mm:ssXXX	2023-11-16T12: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')	16/11/2023 21:08:56 Europe/Paris
=Time.format(Time.now('America/Los Angeles'), 'yyyyMMddHHmm')	202311161208
=Time.format(Time.now('America/Los Angeles'), 'yyyy.MM.dd.HH-mm-ss')	2023.11.16.12-08-56
=Time.format(Time.now('America/Los Angeles'), 'EEE-dd-MM-yy')	Thu-16-11-23
=Time.format(Time.now('America/Los Angeles'), 'hmma')	1208PM
=Time.format(Time.now('Europe/Paris'), 'dd MM \'\'yy hh:mm:ss VV')	16 11 '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/11/16 09:53:08 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/11/16 09:53:08 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.

Example of superfluous repetitions

{
  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 aa (repetition is superfluous, here, as a would have worked as well). This does not work in 9.2.9 and later.

SimpleDateFormat

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

https://docs.oracle.com/en/java/javase/17/docs/api/java.base/java/text/SimpleDateFormat.html

Letter	Date or Time Component	Presentation	Examples
G	Era designator	Text	G - AD
y	Year of era	Number	y, yyy, yyyy - 2023 yy - 23 yyyyy - 02023
Y	week-based-year	year	Y, YYY, YYYY - 2023 YY - 23 YYYYY - 02023
M	Month in year (context sensitive)	Number/Text	M - 3 MM - 03 MMM, MMMM - Mar MMMMM - March {ca}dd MMMM - 02 de març
L	Month in year (standalone)	Number/Text	L - 3 LL - 03 LLL, LLLL - Mar LLLLL - March {ca}dd LLLL - 02 març
w	Week in year	Number	w - 9 ww - 09
W	Week in month	Number	W - 1
D	Day in year	Number	D,DD - 61 DDD - 061
d	Day in month	Number	d - 2 dd - 02
F	Day of week in month	Number	F - 1
E	Day in week	Text	E, `EE - Thursday
u	Day number of week (1 = Monday, ..., 7 = Sunday) - this letter changes in 9.2.9	Number	u - 4 uu - 04
a	AM/PM marker	Text	a - PM
H	Hour in day (0-23)	Number	H - 15 HH - 15
k	Hour in day (1-24)	Number	k - 15 kk - 15
K	Hour in AM/PM (0-11)	Number	K - 3 KK - 03
h	Hour in AM/PM (1-12)	Number	h - 3 hh - 03
m	Minute in hour	Number	m, mm - 45
s	Second in minute	Number	s, ss - 44
S	Millisecond	Number	S, SS, SSS - 998
z	Time zone	General time zone	z, zz, zzz - CET zzzz - Central European Standard Time
Z	Time zone	RFC 822 time zone	Z - +0100 ZZ, ZZZ - +0100
X	Time zone	ISO 8601 time zone	X - +01 XX, XXX - +01:00
i	Olson time zone	Text	i - Europe/Berlin'
{<locale>}	Olson time zone	Text	{de} EEEE - Donnerstag

See Also

• REL Function Library
• Querying the Model with SQL