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.

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.

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:

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.