formatdate IQL function
This IQL function returns a text string formatted according to the specified pattern.
formatdate (fieldname, formatstring), where the field name can be a Date or Date time field, and the format string can be a combination of the patterns in the table below.
Also, it can used in combination with the function parsedate(), to validate a Date or Date time field using a specific format for the date and time; and with parsenumber(), to validate a string (coming from the formatdate) as a numeric value for those cases where you need to do comparisons or concatenations with arithmetic. |
Example 1: Obtain a date value as a string with a specific format.
set "Log Note field" = 'Shipment delivered on ' || formatdate(“Delivery date”, 'Day DD, Month YYYY' ) from "Material order
in this case, the result would be the following string: Shipment delivered on Wednesday 14, October 2020.
Example 2: Set a specific time based on a date.
set "Early appointment" = parsedate(formatdate("Appointment”, 'YYYYMMDD') || '08', 'YYYYMMDDHH')
in this case, formatdate returns e.g. '20230201', then we concat 08 to it so it is '2023020108' which we then feed to parsedate. If minutes/seconds are omitted the default is zero. The "Early appointment" Date time field would be '2023-02-01 08:00:00', according to the user locale.
Example 3: In case you want to set an action based on a comparison on Date, like turning the "Invoice payment due date" field to color red if the date entered is within the current month. For that, in the Behavior property of that Date field in Form designer, type:
if formatdate("Invoice payment due date", 'YYYYMM') = formatdate (today(), 'YYYYMM') then
set :backgroundColor = 'red'
end
You can also set an action instead of Behavior if used with the Run on change property, or the Run on click of a Button component, or in Run IQL in Rules manager.
Example 4: Define relevant date variables in Run IQL, Run on click or Run on change, Run on init or Run on select that you can later use in Form filters. For that, type:
let @firstDayOfCurrentDate = parsedate(formatdate(today(), 'YYYYMM' || '01', 'YYYYMMDD')
/*Assuming that today is 31-05-24, the formatdate above would concatenate 202405 with 01 to have 20240501 which will parse to 01-05-24, and then, the query below would decrease one day to define the last day of the previous month*/
let @lastDayOfPreviousMonth = @firstDayOfCurrentDate - 1
let @firsDayOfPreviousMonth = parsedate(formatdate(@lastDayOfPreviousMonth, 'YYYYMM') || '01', 'YYYYMMDD')
Example 5: Set the last day of the previous month.
set MyDate = parsedate(formatdate(today(), 'YYYYMM') || '01', 'YYYYMMDD') - 1
where the formatdate results in e.g., 202405 and then concatenates with 01 to result in 20240501, converted to a date by parsedate, and subtracting 1 day. Note that the last day could not be directly set on the concatenation because it could vary between 28, 29, 30 or 31 and that is why is a workaround to get to the first day of the current month and then going back one day.
Example 6: Set the last day of the current month.
set MyDate = parsedate(parsenumber(formatdate(today(), 'YYYYMM')) + 1 || '01', 'YYYYMMDD') - 1
in this case, formatdate is returning a string e.g., 202405 that is converted to a number by parsenumber, and then added +1, i.e., 202406. From there, the 01 is concatenated to return as 20240601, then converted to a date by parsedate, and finally subtracted one day to become as last result the date 31-05-2024.
Example 7: Convert the current date time into ISO Date format, e.g., 2025-06-01T08:00:00
formatdate(now(), 'YYYY-MM-DDTHH:MM:SS')
Pattern | Description |
---|---|
YYYY |
year in 4 digits, e.g. 2023 |
YY |
last 2 digits of year, e.g. 23 |
MONTH |
Full month name, e.g. February |
MON |
Short month name, e.g. february. |
DAY |
Full uppercase day name |
DY |
Abbreviated uppercase day name |
DDD |
Day of year (1-366) |
DD |
Day of month (01-31) |
WW |
Week number of year (01-53) Week 1 is the first week with at least 4 days in January |
HH12 |
Hour of 12-hour clock, 01-12 |
HH |
Hour of day (0-23) |
A |
Period of 12-hour clock, i.e. AM or PM |
MI |
Minute (0-59) |
SS |
Second of minute, 00-59 (60 in case of leap second) |