# formatdate IQL function

> Reference for the formatdate IQL function: syntax, the date and time pattern table, and examples combining it with parsedate, e.g. first or last day of a month.

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.

> **Tip:**
> 
> 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](rules_manager.md).

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:MI: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 | Abbreviated month name, e.g. Feb |
| DAY | Full uppercase day name, e.g. Wednesday |
| DY | Abbreviated day name, e.g. Wed |
| DDD | Day of year (1-366) |
| DD | Day of month (01-31) |
| WW | Week number of year (01-53) Week 01 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) |