Built-in Functions → formatDate
formatDate() is a scalar date function that enables you to convert a date formatted string using simple formatting characters.
Signature
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.
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.
Returns
String
date in the string mask
format you entered.
Examples
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" | 11 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:
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"
- Replace
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.