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 the formats to use for column values using pattern string notation. See Changing the Way Data is Formatted in a Designer Report.

In Visualizer you select formats from the UI.

These pattern string formats are supported by Infor Birst.

Sometimes the same character is used to identify very different fields, with capitalization or number instances creating the different variations. This table shows some guidelines for formatting dates:

Field Variations
Month
  • m = minutes
  • M = months
  • M = the month number (7)
  • MM = the month number with a leading zero (07)
  • MMM = the abbreviated name of the month (Jul)
  • MMMM = the full name of the month (July)
Day
  • d = day of month (4)
  • dd = the day number with a leading zero (04)
  • D =r day of year. D is rarely used.
Hour
  • H is 24 hour time
  • h is 12 hour time
  • Using hh without aaa can be very confusing.
Time Zone
  • Z = timezone offset from GMT (-0700)
  • z= the 3 character ISO timezone identifier (GMT)
  • zzzz = the long form name (Greenwich Mean Time)
.
Day of the week
  • EEE = the abbreviated name for the day of the week (Wed)
  • EEEE = the full name (Wednesday)
Numeric items 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 ensure that there is no confusion about the century or confusion about year and month or day