Data Type Formatting Functions

Data type formatting functions are used to convert various data types into formatted strings, and vice versa. The data type formatting functions in Relyt all follow a common calling convention: the first argument is the value to be formatted, and the second argument is a template that defines the format for the output or input.

Extreme DPS provides several date/time formatting functions, as described in the following table.

Function	Return type	Description
TO_CHAR(date, text)	text	Converts the date to a string.
TO_CHAR(timestamp with time zone, text)	text	Converts the timestamp with time zone to a string of the specified format.
TO_CHAR(timestamp, text)	text	Converts the timestamp to a string of the specified format.
TO_DATE(text, text)	date	Converts a string to a date of the specified format.

---

Supported template patterns for date/time formatting

The following table describes the template patterns that can be used for date/time formatting.

Pattern	Description	TO_CHAR	TO_DATE	TO_TIMESTAMP
HH	Hour, ranging from 01 to 12.	✅	N/A	✅
HH	Hour, ranging from 01 to 12.	✅	N/A	✅
HH24	Hour, ranging from 00 to 23.	❌	N/A	✅
MI	Minute, ranging from 00 to 59.	✅	N/A	✅
SS	Second, ranging from 00 to 59.	✅	N/A	✅
MS	Millisecond, ranging from 000 to 999.	✅	N/A	✅
US	Microsecond, ranging from 000000 to 999999.	✅	N/A	✅
AM, am, PM, or PM	Meridiem indicator, AM or am for forenooon and PM or pm for afternoon.	Only AM and PM supported	N/A	✅
YYYY	Year (4 or more digits).	✅	✅	✅
YY	Last two digits of the year.	✅	✅	✅
IYYY	ISO 8601 week-numbering year (4 or more digits).	✅	❌	❌
BC, bc, AD, or ad	Era indicator, BC for Before Christ and AD for Anno Domini.	Only BC and AD supported	✅	✅
MONTH, Month or month	Full name of the month (padded with spaces to 9 characters).	✅	✅	✅
MON, Mon or mon	Abbreviation of the month (3 characters in English, the abbreviated length may vary in different languages).	✅	✅	✅
MM	Month number in the year, ranging from 01 to 12.	✅	✅	✅
DAY, Day or day	Full name of the day (padded with spaces to 9 characters).	✅	✅	✅
DY, Dy or dy	Abbreviation of the day (3 characters in English, the abbreviated length may vary in different languages).	✅	✅	✅
DDD	Day of year, ranging from 001 to 366.	✅	✅	✅
IDDD	Day of ISO 8601 week-numbering year, ranging from 001 to 371 (the first day of the year is the Monday of the first ISO week).	✅	❌	❌
DD	Day of month, ranging from 01 to 31.	✅	✅	✅
D	Day of the week, ranging from 1 (Sunday) to 7 (Saturday).	✅	✅	✅
ID	ISO 8601 day of the week, ranging from 1 (Monday) to 7 (Sunday).	✅	❌	❌
W	Week of month, ranging from 1 to 5 (the first week starts on the first day of the month).	✅	✅	✅
WW	Week of year, ranging from 1 to 53 (the first week starts on the first day of the year).	✅	✅	✅
IW	Week of ISO 8601 week-numbering year, ranging from 01 to 53 (the first Thursday of the year is in week 1).	✅	❌	❌
Q	The quarter.	✅	✅	✅
TZ	Time-zone abbreviation.	✅	❌	N/A
TZH	Time-zone hours.	✅	N/A	✅
TZM	Time-zone minutes.	✅	N/A	✅
OF	Time-zone offset from UTC.	✅	❌	N/A

---

Supported pattern modifiers

The following table describes the pattern modifiers supported in the date/time formatting template. TO_CHAR supports all the modifiers listed below, while TO_DATE only supports the FX prefix.

Modifier	Description	Example
FM prefix	A fill mode that supresses leading zeros and padding spaces.	TO_CHAR(date '2023-02-05', 'YYYY-FMMM-FMDD') → 2023-2-5
TH suffix	A suffix (uppercase) added to ordinal numbers to indicate sequence.	TO_CHAR(date '2023-02-05', 'YYYY MMTH DDTH') → 2023 02ND 05TH
th suffix	A suffix (lowercase) added to ordinal numbers to indicate sequence.	TO_CHAR(date '2023-02-05', 'YYYY MMth DDth') → 2023 02nd 05th
FX prefix	A fixed format global option. For more information, see see Usage notes.	FX Month DD Day

---

Usage notes

• FM suppresses leading zeroes and trailing blanks that would otherwise be added to make the output of a pattern be fixed-width.

• TO_DATE skip multiple blank spaces at the beginning of the input string and around date and time values unless the FX option is used. For example, TO_DATE(' 2023 DEC', 'YYYY MON') work, but TO_DATE(' 2023 DEC', 'FXYYYY MON') returns an error because TO_DATE expects only a single space. FX must be specified as the first item in the template.

• A separator (a space or non-letter/non-digit character) in the template string of TO_DATE matches any single separator in the input string or is skipped, unless the FX option is used. For example, TO_DATE('2023DEC', 'YYYY!#@#$%^\*MON') work, but TO_DATE('2023!!!DEC', 'YYYY!MON') returns an error because the number of separators in the input string exceeds the number of separators in the template. If FX is specified, a separator in the template string matches exactly one character in the input string. But note that the input string character is not required to be the same as the separator from the template string. For example, TO_DATE('2023/DEC', 'FXYYYY MON') works, but TO_DATE('2023/DEC', 'FXYYYY MON') returns an error because the second space in the template string consumes the letter D from the input string.

• Ordinary text is allowed in TO_CHAR templates and will be output literally. You can put a substring in double quotes to force it to be interpreted as literal text even if it contains template patterns. For example, in '"Hello YY "YYYY', the YYYY will be replaced by the year data, but the YY in "Year YY" will not be. In TO_DATE, literal text and double-quoted strings result in skipping the number of characters contained in the string; for example "XX" skips two input characters (whether or not they are XX).

• If you want to have a double quote in the output you must precede it with a backslash, for example TO_CHAR(date '2023-12-23', '\"YYYY Month\"') -> "2023 December ". Backslashes are not otherwise special outside of double-quoted strings. Within a double-quoted string, a backslash causes the next character to be taken literally, whatever it is (but this has no special effect unless the next character is a double quote or another backslash).

• In TO_DATE, if the year format specification is less than four digits, for example, YYY, and the supplied year is less than four digits, the year will be adjusted to be nearest to the year 2020, for example, 95 becomes 1995.

• An input of year zero is treated as 1 BC.

• In TO_DATE, the YYYY conversion has a restriction when processing years with more than 4 digits. You must use some non-digit character or template after YYYY, otherwise the year is always interpreted as 4 digits. For example (with the year 20000): TO_DATE('200000915', 'YYYYMMDD') will be interpreted as a 4-digit year; instead use a non-digit separator after the year, like TO_DATE('20000-0915', 'YYYY-MMDD') or TO_DATE('20000Sep15', 'YYYYMonDD').