FORMAT Function

The FORMAT function in BQL formats a measure or date attribute to a specified string format.

FORMAT([measure|date_attribute],'format_string')

In a SELECT statement, surround the FORMAT function in parenthesis.

SELECT (FORMAT([Orders.Order_Date], 'EEEE, d MMM yyyy HH:mm:ss Z')) FROM [All]

The FORMAT function returns a varchar.

Example of Measure Formatting

For format string syntax, see Number, Date, and Time Pattern String Formats.

  • Example query without formatting:

    SELECT [Order_Date: Sum: Quantity] FROM [All]

    Result: 944,592

  • Example query with formatting:

    SELECT (FORMAT([Order_Date: Sum: Quantity], '##,###,##0.00')) FROM [All]

    Result: 944,592.00

Example of Date Attribute Formatting

For date formatting syntax, see Number, Date, and Time Pattern String Formats

  • Example query without formatting:

    SELECT [Orders.Order_Date] FROM [All]

    Result:

    9/6/11

    4/21/13

    ...

  • Example query with formatting:

    SELECT (FORMAT([Orders.Order_Date], 'yyyy-MM-dd')) FROM [All]

    Result

    2011-01-01

    2011-01-12

    ...

Tips

  • In Visualizer, set the format of a measure column using the Data Formatting panel.

    Date Formatting panel

    Set the format of a date attribute in the Date Formats panel:

    Data Formats
  • In Designer you can set the format of a column:Column Properties
  • Designer supports a special function called TO_TIME_FORMAT that converts an integer to a time format. This applies only in the Designer Column Properties dialog.