# 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

```apteco
FormatDate([Date Variable],"Format Specifier")
```

Expression

```apteco
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

```apteco
DateToNumber(Date)
```

For example:

Expression

```apteco
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

```apteco
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

```apteco
FinancialDate(Date, "Format") 
```

The result depends on the configured financial year start date.

For example:

Expression

```apteco
FinancialDate(MakeDate(20140404))
```

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

## StringToDate

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

Expression

```apteco
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

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

Returns a date value for 29<sup>th</sup> September 2025.

Expression

```apteco
StringToDate("2025-135", "yyyy.ddd")
```

Identifies the 135<sup>th</sup> day of 2025 and returns 15<sup>th</sup> May 2025.