DateFormat (PQL)
Formats date-time values into strings using the chosen format or 'mask'.
- Returned Output: Text
- Library: PQL \ Granular \ Date-Time
- Version: 2023.00.000
- Compatibility: Pyramid Query Language (PQL) data sources
Syntax
DateFormat( <Column DateTime> , <Text> )
* Click on the function's arguments above for more details on the input values.
Comments
- The input column needs to be an attribute with date-time values in the model. (The data type needs to be 'date-time')
- Text is a mask describing how to format the date strings (see below)
- The resulting column is a text or string value - NOT a date or time.
- For details on how to employ and use this function see the custom column overview.
Text Masks
The masked items can be used in combination to create the format.
- yyyy - 4 digit numeric year (2023)
- yy - 2 digit numeric year (23)
- MMMM - full month name (March)
- MMM - 3 character month (Mar)
- MM - 2 digit numeric month (03)
- M - single digit numeric month (3)
- dd - 2 digit numeric days (04)
- d - single digit numeric day (4)
- qq - 2 digit numeric quarter (04)
- q - single digit numeric quarter (4)
- ww - 2 digit numeric week (01)
- HH - 2 digit numeric hour - 24 hour clock (14)
- hh - 2 digit numeric hour - 12 hour clock (02)
- mm - 2 digit numeric minute
- ss - 2 digit numeric second
Different Function types
- As a granular function, it is used in the base SQL query itself.
Examples
This example returns a column with the number of years between the current date and the date in the input column (order date).
Dateformat([transactions].[Order Date],"yy-MMM-dd hh.mm")
In the example below, the 6th column represents the format of the order date column (column 1) using the formula above,