With the DateFormat function, a date or time input can be converted into another date or time format. One input field is necessary for DateFormat.
Parameters
| Source format | Defines the date format of the input field. Example: “dd.MM.yyyy” |
| Target format | Defines the date format of the output field. Example: “MMM” |
| Default value | Defines a fallback value when the incoming value is null. |
| Language | Sets the input and output language. The language should be set as a combination of a standard language code and of a standard country code. The pattern is <language>_<country>, also possible is only <language>. For example, English can be set as If <country> or both codes are not set, for the missing code will be set the corresponding system default value. |
| Complete week | If the box is checked, then the first or last week of the year will contain 7 days and might contain dates from 2 years, i.e. 2018 and 2019.
If the box is not checked, then both the first and last weeks of the year may contain less than 7 days, but each week will only contain days from a single year. |
| First day of the week | Sets the first day of the week. Sunday=1, Monday=2, ... Saturday=7. If not set, the language-specific value is used, e.g. 1 in US and 2 in France. Note: this parameter is only required if weeks are part of the source or target format. |
| Minimum of days in first week | The minimal days required for the first week of the year. For example, if the first week is defined as one that contains the first day of the first month of a year, it is 1. If it must be a full week, it is 7. If not set, the language-specific value is used. Note: this parameter is only required if weeks are part of the source or target format. |
Notes:
-
When using the DateFormat function on source data which cannot be parsed as a Date and a default value of NULL or empty is given, then an Info message is raised, and an empty string is returned.
-
If the new date format is to contain fixed text elements, this can be defined with single quotation marks ('Text').
Details on date format schema
Letters from A to Z (and a to z) that are not in quotation marks will be interpreted as text patterns representing a date or time. To avoid being interpreted as a time pattern, text can be given in single quotation marks ('). All other symbols are simply copied into the display string (for Target format) or compared with the entry (for Source format). For example, for 2020-01-15 T13:32:22, the source format needs to be entered as 2020-01-15 'T'13:32:22 so that the string gets parsed correctly.
Source format: yyyy-MM-dd 'T'H:m:s
Target format: yyyy-MM-dd H:m:s
Sample characters are generally repeated, as the number thereof defines the exact display. For example, if the number of sample characters is 4, the full form is used for text (e.g. "January"). A shortened form (e.g. "Jan") will be used if it exists. For numbers, the number of sample characters is the minimum number of digits shown. An appropriate number of zeros is therefore added to the beginning of smaller numbers.
DateFormat functions with short month as the target format (i.e. MMM yyyy) return the date in the form e.g. "Jan. 2018" (see screenshot below). Jedox uses OpenJDK 17.0.9, which uses CLSR unicode standards. This formatting is specific for only a few languages, including German.
If the source format pattern contains "MMM", the short month parsing will not have the same functionality in all languages. For example, if you use the pattern "MMM yy" with input data "Jan 08", it will be displayed correctly in English ("en"), but not in German ("de"). The input data "Jan. 08" will be displayed correctly in German ("de"), but not in English ("en").
The function DateFormat recognizes the letters of the standard SimpleDateFormat (not all are useful for this function).
The letters q, Q, and v are Jedox Integrator-specific enhancements to the standard SimpleDateFormat, as well as the patterns cd, cM, and cQ. These patterns can only be used as the target format.
Below is an overview of the most used letters for the function DateFormat:
| Letter | Meaning | Result for 31.12.2014 |
| a | am/pm marker | PM |
| cd | Day counter starting from 1900, like in MS Excel serial days | 42004 |
| cM | Month counter starting from 1900 | 1380 |
| cQ | Quarter counter starting from 1900 | 460 |
| d | Day in month | 31 |
| D | Day in year | 365 |
| E | Day in week | EEE: Wed, EEEE: Wednesday |
| H | Hour in day (0-23) | 0 |
| k | Hour in day (1-24) | 24 |
| K | Hour in am/pm (0-11) | 0 |
| h | Hour in am/pm (1-12) | 12 |
| m | Minute in hour | 30 |
| M | Month in year | MM: 12, MMM: Dec, MMMM: December |
| q | Half year | 2 |
| Q | Quarter in year | 4 |
| s | Second in minute | 55 |
| S | Millisecond | 978 |
| v | Week in year (corresponding to y) | 53 |
| w | Week in year (corresponding to Y) | 1 |
| W | Week in month | 5 |
| y | Year | yyyy: 2014, yy: 14 |
| Y | Week year | 2015 |
Examples
| dd.MM.yyyy | 04.09.2008 |
| EEE, d MMM yyyy | Wed, 4 Sep 2008 |
| MMMM | September |
Updated March 27, 2024