FORMAT Function

The BQL FORMAT function 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 Measure Formatting

For format string syntax see 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 Date Attribute Formatting

For date formatting syntax see 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.
  
  Set the format of a date attribute in the Date Formats panel.
  
  

• In Designer you can set the format of a column using the Column Properties dialog. See Changing the Way Data is Formatted in a Report.
  
• Designer saved expressions can use the Column Properties Format option. If a saved expression is a Datetime data type, you can select formats from the pulldowns.
  
• 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. See TO_TIME_FORMAT Formatting Function.