Skip to content

Date conversion functions

To use a date as a string in an expression it is necessary to specify the format. Dates are formatted into strings using the FormatDate function.

FormatDate

Converts a Date or DateTime variable to its string representation.

Expression
FormatDate([Date Variable],"Format Specifier")
Expression
FormatDate([DateTime Variable],"Format Specifier")

The following expressions demonstrate its use:

Expression Result
FormatDate([Date Variable],”%d-%m-%Y”) 25-05-2021
FormatDate([Date Variable],””) 2021-05-25
FormatDate([Date Variable],”%A, %B %#d”) Tuesday, May 25
FormatDate([Date Variable],”Date: %#x”) Date: 25 May 2021
FormatDate([DateTime Variable],”Day of year: %j, Time: %X”) Day of year: 005, Time: 21:34:43

The format specifiers are detailed below, with examples of the output produced by each format specifier.

Format Specifier Description Example Output
%a Abbreviated weekday name Wed
%A Full weekday name Wednesday
%b Abbreviated month name Dec
%B Full month name December
%c Date and time representation appropriate for locale 29/12/2021 21:09:08
%d Day of month as decimal number (01 – 31) 29
%H Hour in 24-hour format (00 - 23) 21
%I Hour in 12-hour format (01 - 12) 09
%j Day of year as decimal number (001 – 366) 363
%m Month as decimal number (01 – 12) 12
%M Minute as decimal number (00 - 59) 09
%p Current locale's a.m./p.m. indicator for 12-hour clock PM
%S Second as decimal number (00 - 59) 08
%U Week of year as decimal number, with Sunday as first day of week (00 – 53) 52
%w Weekday as decimal number (0 – 6; Sunday is 0) 3
%W Week of year as decimal number, with Monday as first day of week (00 – 53) 52
%x Date representation for current locale 29/12/2021
%X Time representation for current locale 21:09:08
%y Year without century, as decimal number (00 – 99) 21
%Y Year with century, as decimal number 2021
%% Percent sign %

The # flag may prefix any formatting code. In that case, the meaning of the format code is changed as follows.

Format Code Meaning
%#x Long date representation, appropriate to current locale. For example: “Tuesday, March 14, 1995”.
%#d, %#H, %#I, %#j, %#m, %#M, %#S, %#U, %#w, %#W, %#y, %#Y Remove leading zeros (if any).

DateToNumber

Converts the date to a numeric value in the form YYYYMMDD.

Expression
DateToNumber(Date)

For example:

Expression
DateToNumber([Order Date])

Converts the order date as a numeric value YYYYMMDD.

DateISO

Turns a date or datetime value into a formatted string representing the date in ISO 8601 format (YYYY-Www-d).

Expression
DateISO([Date])

For example, 31-12-2010 returns 2010-W52-5.

FinancialDate

Turns a date or datetime value into a formatted string representing the financial date, where the “Format” is one of years, quarters, months, weeks, or days.

Expression
FinancialDate(Date, "Format")

The result depends on the configured financial year start date.

For example:

Expression
FinancialDate(MakeDate(20140404))

Returns 2013-2014 W52 if the configured start date of the year is the 6th April.

StringToDate

Returns a date value from the string in the first parameter, given the format in the second parameter.

Expression
StringToDate(DateString, DateFormat)

Note

Invalid values will return a missing date.

The following are valid date formats for the second parameter, which must be specified exactly:

  • ”dd.mm.yyyy”
  • ”mm.dd.yyyy”
  • ”dd.mmm.yyyy”
  • ”mmm.dd.yyyy”
  • ”yyyy.ddd”
  • ”yyyy.mm.dd”

For example:

Expression
StringToDate("29-09-2025", "dd.mm.yyyy")

Returns a date value for 29th September 2025.

Expression
StringToDate("2025-135", "yyyy.ddd")

Identifies the 135th day of 2025 and returns 15th May 2025.