Number, Date, and Time Pattern String Formats

BQL supports pattern string formatting for numbers, dates, and times. For example, using the FORMAT function in a BQL expression you can specify the format of the result of a SELECT statement. See FORMAT.

In Designer you can specify, using pattern string notation, the formats to use for column values. See Changing the Way Data is Formatted in a Designer Report.

In Visualizer you select formats from the UI.

Birst supports the following pattern string formats.

Number Formats
Date and Time Formats
- Date Format Examples
- Tips for Formatting Dates

Number Formats

Number formats are used for amounts and percentages.

Format	Used For
#,###,###.##	Amounts. Will put a comma in for the 1000’s and if there are decimals will only go two places.
#,###,###.00	Amounts. The zeros force two decimal places.
##,###,###.00%	Percentage (automatically multiplies by 100).
$##,###,###	Dollar amounts.

Date and Time Formats

Date and time formats are specified by date and time pattern strings. Within date and time pattern strings, unquoted letters from A to Z and from a to z are interpreted as pattern letters representing the components of a date or time string, as defined in the table below. Text that you want included in the output should be quoted using single quotes (').

Letter	Date or Time Component	Presentation	Examples
G	Era designator	Text	`AD`
`y`	Year	Year	`1996`; `96`
`Y`	Week year	Year	`2009`; `09`
`M`	Month in year	Month	`July`; `Jul`; `07`
`w`	Week in year	Number	`27`
`W`	Week in month	Number	`2`
`D`	Day in year	Number	`189`
`d`	Day in month	Number	`10`
`F`	Day of week in month	Number	`2`
`E`	Day name in week	Text	`Tuesday`; `Tue`
`u`	Day number of week (1 = Monday, ..., 7 = Sunday)	Text	`1`
`a`	AM/PM marker	Text	`PM`
`H`	Hour in day (0-23)	Number	`0`
`k`	Hour in day (1-24)	Number	`24`
`K`	Hour in AM/PM (0-11)	Number	`0`
`h`	Hour in AM/PM (1-12)	Number	`12`
`m`	Minute in hour	Number	`30`
`s`	Second in minute	Number	`55`
`S`	Millisecond	Number	`978`
`z`	Time zone	General time zone	`Pacific Standard Time`; `PST`; `GMT-08:00`
`Z`	Time zone	RFC 822 time zone	`-0800`
`X`	Time zone	ISO 8601 time zone	`-08`; `-0800`; `-08:00`

The number of pattern letters determines the exact presentation:

Text: If the number of pattern letters is 4 or more, the full form is used; otherwise a short or abbreviated form is used if available.
Number: The number of pattern letters is the minimum number of digits, and shorter numbers are zero-padded to this amount.
Year: If the number of pattern letters is 2, the year is truncated to 2 digits; otherwise it is interpreted as a number.
Month: If the number of pattern letters is 3 or more, the month is interpreted as text; otherwise, it is interpreted as a number.

General time zone: Time zones are interpreted as text if they have names. For time zones representing a GMT offset value, the following syntax is used:

     GMTOffsetTimeZone:
             GMT  Sign  Hours  :  Minutes
     Sign: one of
             + -
     Hours:
             Digit
             Digit  Digit
     Minutes:
             Digit  Digit
     Digit: one of
             0 1 2 3 4 5 6 7 8 9

Hours must be between 0 and 23, and Minutes must be between 00 and 59. The format is locale independent and digits must be taken from the Basic Latin block of the Unicode standard.

For parsing, RFC 822 time zones are also accepted.

RFC 822 time zone: For formatting, the RFC 822 4-digit time zone format is used:

     RFC822TimeZone:
             Sign  TwoDigitHours  Minutes
     TwoDigitHours:
             Digit Digit

TwoDigitHours must be between 00 and 23.

Date Format Examples

The following examples show how date and time patterns are interpreted in the U.S. locale. The date and time used for the examples is 2011-07-04 12:08:56 local time in the U.S. Pacific Time time zone.

Date and Time Pattern	Result
EEEE, d MMM yyyy HH:mm:ss Z	Wednesday, 4 Jul 2011 12:08:56 -0700
yyyy-MM-dd	2011-07-04
MM/dd/yyyy	07/04/2011
EEE, d MMM yyyy HH:mm:ss Z	Wed, 4 Jul 2011 12:08:56 -0700
yyMMddHHmmssZ	150704120856-0700
yyyy.MM.dd G 'at' HH:mm:ss z	2015.07.04 AD at 12:08:56 PDT
EEE, MMM d, ''yy	Wed, Jul 4,'15
yyyyy.MMMMM.dd GGG hh:mm aaa	02015.July.04 AD 12:08 PM
h:mm a	12:08 PM
K:mm a, z	0:08 PM, PDT
hh 'o''clock' a, zzzz	12 o'clock PM, Pacific Daylight Time

Tips for Formatting Dates

Sometimes the same character is used to identify very different fields, with capitalization or number instances creating the different variations. Here are some tips for formatting dates:

m is for minutes, M is for months. M means the month number (7), MM means the month number with a leading zero (07), MMM means the abbreviated name of the month (Jul), MMMM means the full name of the month (July).
d is for day of month (4), dd is the day number with a leading zero (04), D is for day of year. D is rarely used.
H is 24 hour time, h is 12 hour time. Using hh without aaa can be very confusing.
Z is for timezone offset from GMT (-0700), z is for the 3 character ISO timezone identifier (GMT), zzzz is for the long form name (Greenwich Mean Time).
EEE is for the abbreviated name for the day of the week (Wed), EEEE is for the full name (Wednesday).
For most numeric items, use 2 or 4 instances of the character to force a leading zero if the number can be 1 or 2 digits. Use 4 for year to make sure there is no confusion as to the century or confusion with year and month or day.