Expression: 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
FormatDate([Date Variable],"Format Specifier") 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 |
|
%z, %Z |
Either the time-zone name or time zone abbreviation, depending on registry settings; no characters if time zone is unknown |
GMT Standard Time |
|
%% |
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). |
The following additional Date Conversion functions are available:
| DateToNumber |
DateToNumber(Date)
Converts the date to a numeric value YYYYMMDD.
e.g. DateToNumber([Order Date]) - converts the order date into a numeric value YYYYMMDD.
|
| DateISO |
DateISO(Date)
Turns a date or datetime value into a formatted string representing the date in ISO 8601 format (YYYY-Www-d).
e.g. 31-12-2010 - returns 2010-W52-5.
|
| FinancialDate |
FinancialDate(Date, "Format")
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. The result depends on the configured financial year start date.
e.g. FinancialDate(MakeDate(20140404) - returns "2013-2014 W52" if the configured start date of the year is the 6th April.
|