Overview
In scenarios where frequent format conversion is required during data analysis, FineBI provides the FORMAT function specially for field formatting.
Grammar | FORMAT(object,format) | Returns an object in a specified format. |
Parameter 1 | object | Object to be formatted, the type of which can be String, Number, or Object (Date, Time commonly used)  Note: |
Parameter 2 | format | Object format |
Notes
The function supports two parameters. For extracted data, the first parameter can be of any type and the second is of the text type.
For real-time data, the first parameter is of the date type and the second is of the text type.
To format date through the FORMAT function, date must be in yy/yyyy - M/MM - d/dd format.
The FORMAT function is not supported for the direct connect Kyligence database (enterprise edition).
Example
The following section takes 2021-06-15 as an example to introduce common FORMAT parameters, including date-type and numeric parameters.
Date Format Parameter
| Format Parameter | Output |
|---|---|
| D | 166 |
| DD | 166 |
| M | 6 |
| MM | 06 |
| MMM | June |
| MMMM | June |
| YY | 21 |
| YYYY | 2021 |
| YYYYMM | 202106 |
| MM/dd/yyyy | 06/15/2021 |
| M-d-yy | 6-15-21 |
| EEEEE, MMMMM dd, yyyy | Tuesday, June 15, 2021 |
| h:mm:ss a | 12:00:00 A.M. |
For example, if you want to remove the hour data from the date string,
you can create a new formula column, name it, and enter the formula TODATE(FORMAT(column to be formatted,"yyyy-MM-dd")). The result is shown in the figure below.
Numeric Format Parameter
Take 1234.56 as an example.
| Parameter Format | Formula | Output |
|---|---|---|
| #,##0.00 | FORMAT(1234.5, "#,##0.00") | 1234.50 |
| "#,##0 | FORMAT(1234.5, "#,##0") | 1234 |
| ￥#,##0.00 | FORMAT(1234.5, "￥#,##0.00") | ￥1234.50 |
| 0% | FORMAT(1.5, "0%") | 150% |
| 0.000% | FORMAT(1.5, "0.000%") | 150.000% |
| ##0.0E0 | FORMAT(6789, "##0.0E0") | 6.789E3 |
| 0.00E00 | FORMAT(6789, "0.00E00") | 6.79E03 |
Combination with Other Functions
| Formula | Result | Notes |
|---|---|---|
| FORMAT(date(2007,1,1), "EEEEE, MMMMM dd, yyyy") | Monday, January 01, 2007 | |
| FORMAT(date(2007,1,13), "MM/dd/yyyy") | 01/13/2007 | |
| FORMAT(date(2007,1,13), "M-d-yy") | 1-13-07 | |
| FORMAT(time(16,23,56), "h:mm:ss a") | 4:23:56 P.M. |
