Built-in Functions → formatDate

formatDate() is a scalar date function that enables you to convert a date formatted string using simple formatting characters.

formatDate(date exp,string mask)

The following table illustrates the formatDate() scalar date function parameters:

Parameter	Description
date exp	The input date value to format
string mask	A string of characters (usually repeated) to determine the format of the output.

The following table indicates the characters that you can use in writing the string mask value.

Important

The string mask value is case-sensitive.

character	Description	Type	Output Examples
G	Era designator	Text	BC
y	Year	Year	2018; 18
Y	Week year	Year	2001; 01
M	Month in year (context sensitive)	Month	April; Apr; 04
L	Month in year (standalone form)	Month	August; Aug; 08
w	Week in year	Number	30
W	Week in month	Number	3
D	Day in year	Number	200
d	Day in month	Number	16
F	Day of week in month	Number	5
E	Day name in week	Text	Monday; Mon
u	Day number of week (1 = Monday, ..., 7 = Sunday)	Number	5
a	Am/pm marker	Text	AM
H	Hour in day (0-23)	Number	23
k	Hour in day (1-24)	Number	21
K	Hour in am/pm (0-11)	Number	11
h	Hour in am/pm (1-12)	Number	1
m	Minute in hour	Number	22
s	Second in minute	Number	44
S	Millisecond	Number	567
z	Time zone	General time zone	Coordinated Universal Time; UTC; GMT+09:00
Z	Time zone	RFC 822 time zone	+1100
X	Time zone	ISO 8601 time zone	-05; -0500; -05:00

For more information, refer to the Date and Time Patterns documentation.

String date in the string mask format you entered.

The following are result examples that depend on the string mask parameter you entered in the function:

string mask value	Output result
`"yyyy.MM.dd G 'at' HH:mm:ss z"`	`2001.07.20 AD at 11:09:40 PDT`
`"EEE, MMM d, ''yy"`	`Thu, Jul 5, '01`
`"h:mm a"`	`01:08 PM`
`"hh 'o''clock' a, zzzz"`	1`1 o'clock PM, Pacific Daylight Time`
`"K:mm a, z"`	`0:09 AM, PDT`
`"yyyyy.MMMMM.dd GGG hh:mm aaa"`	`02001.July.04 AD 12:08 PM`
`"EEE, d MMM yyyy HH:mm:ss Z"`	`Tue, 3 Jul 2001 11:08:56 -0600`
`"YYYY-'W'ww-u"`	`2002-W28-4`

Use the following steps for detailed instructions on how to use the formatDate() scalar function:

Note

Use the sample data provided with Incorta when you set up a tenant.

Create a new insight using the SALES table as your sample data.
From the Data** **panel, drag and drop the following columns to the respective tray:
- From the PRODUCTS table, drag and drop Prod. Subcategory to the Grouping Dimension tray.
- From the SALES table, drag and drop Revenue to the Measure tray. In the Properties panel,
  - For Format, select Dollar Rounded.
From the Data panel, drag and drop Add Formula as the first pill to the Grouping Dimension tray.
The Formula Builder automatically opens:
- In Search Functions, select the down arrow, and then select Date Functions.
- Double-click the formatDate function, formatDate(date exp, string mask), to add the formula to the editor.
- In the Formula Editor,
  - Replace date exp with Sales Date from the Data panel
  - Replace string mask with "EEE, MMM d, ''yy"

formatDate(SALES.SALES.TIME_ID, "EEE, MMM d, ''yy")

Select Validate & Save.
In the Measure tray, double-click the New Formula pill and rename it to formatDate().
Name the insight: Products Revenue.
In the Action bar, select Save.