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:

ParameterDescription
date expThe input date value to format
string maskA 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.

characterDescriptionTypeOutput Examples
GEra designatorTextBC
yYearYear2018; 18
YWeek yearYear2001; 01
MMonth in year (context sensitive)MonthApril; Apr; 04
LMonth in year (standalone form)MonthAugust; Aug; 08
wWeek in yearNumber30
WWeek in monthNumber3
DDay in yearNumber200
dDay in monthNumber16
FDay of week in monthNumber5
EDay name in weekTextMonday; Mon
uDay number of week (1 = Monday, ..., 7 = Sunday)Number5
aAm/pm markerTextAM
HHour in day (0-23)Number23
kHour in day (1-24)Number21
KHour in am/pm (0-11)Number11
hHour in am/pm (1-12)Number1
mMinute in hourNumber22
sSecond in minuteNumber44
SMillisecondNumber567
zTime zoneGeneral time zoneCoordinated Universal Time; UTC; GMT+09:00
ZTime zoneRFC 822 time zone+1100
XTime zoneISO 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 valueOutput 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.