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 - four digit numeric year (2023)
  • yy - two digit numeric year (23)
  • MMMM - full month name (March)
  • MMM - three character month (Mar)
  • MM - two digit numeric month (03)
  • M - single digit numeric month (3)
  • dd - two digit numeric days (04)
  • d - single digit numeric day (4)
  • qq - two digit numeric quarter (04)
  • q - single digit numeric quarter (4)
  • ww - two digit numeric week (01)
  • HH - two digit numeric hour - 24 hour clock (14)
  • hh - two digit numeric hour - 12 hour clock (02)
  • mm - two digit numeric minute
  • ss - two digit numeric second
  • e - day of week number, single digit where first day of week is Sunday (1=Sunday - 7=Saturday)
  • ee - day of week number, double digit where first day of week is Sunday (01=Sunday - 07=Saturday)
  • isoe - day of week number, single digit where first day of week is Monday (1=Monday - 7=Sunday)
  • isoee - day of week number, double digit where first day of week is Monday (01=Monday - 07=Sunday)
  • EEE - day of week short name (Mon)
  • EEEE - day of week long name (Monday)
Different Function types

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,

Tip: You could prefix the value in the preceding date field with the short version of the day of the week by adding the following:

Dateformat([transactions].[Order Date],"EEE yy-MMM-dd hh.mm")

This would make the first field in the dateformat column (above): Wed 15-Dec-16 06.17.