Oracle® Business Intelligence Answers, Delivers, and Interactive Dashboards User Guide > Formatting Results in Oracle BI Answers >

Using Custom Date/Time Format Strings in Oracle BI Answers


Custom date/time format strings provide additional options for formatting columns that contain timestamps, dates, and times. This section contains the following topics:

To enter a custom date/time format string in Oracle BI Answers

  1. From the Criteria tab in Oracle BI Answers, click the Format Column button for a column that contains a timestamp, a date, or a time.

    The Column Properties dialog box opens.

  2. Select the following option in the Data Format area:

    Override Default Data Format

  3. In the Date Format field, select Custom from the drop-down list.
  4. In the Custom Date Format field, type the custom format string exactly as shown in the following tables, including left and right bracket characters ([ ]).

    NOTE:  You must type the custom format string into the Custom Date Format field. Custom format strings are not available for selection from the drop-down list.

Oracle BI Answers General Custom Format Strings

Table 8 shows the general custom format strings and the results they display. These allow the display of date/time fields in the user's locale.

Table 8. General Custom Format Strings
General Format String
Result

[FMT:dateShort]

Formats the date in the locale's short date format. You can also type [FMT:date].

[FMT:dateLong]

Formats the date in the locale's long date format.

[FMT:dateInput]

Formats the date in a format acceptable for input back into the system.

[FMT:time]

Formats the time in the locale's time format.

[FMT:timeHourMin]

Formats the time in the locale's time format but omits the seconds.

[FMT:timeInput]

Formats the time in a format acceptable for input back into the system.

[FMT:timeInputHourMin]

Formats the time in a format acceptable for input back into the system, but omits the seconds.

[FMT:timeStampShort]

Equivalent to typing [FMT:dateShort] [FMT:time]. This formats the date in the locale's short date format and the time in the locale's time format. You can also type [FMT:timeStamp].

[FMT:timeStampLong]

Equivalent to typing [FMT:dateLong] [FMT:time]. This formats the date in the locale's long date format and the time in the locale's time format.

[FMT:timeStampInput]

Equivalent to [FMT:dateInput] [FMT:timeInput]. This formats the date and the time in a format acceptable for input back into the system.

[FMT:timeHour]

Formats the hour field only in the locale's format, such as 8 PM.

Oracle BI Answers ODBC Custom Format Strings

Table 9 shows the ODBC standard typed custom format strings and the results they display. These display date/time fields according to the ODBC standard.

Table 9. ODBC Custom Format Strings
ODBC Format String
Result

[FMT:dateODBC]

Formats the date in standard ODBC yyyy-mm-dd format (4-digit year, 2-digit month, 2-digit day).

[FMT:timeODBC]

Formats the time in standard ODBC hh:mm:ss format (2-digit hour, 2-digit minute, 2-digit second).

[FMT:timeStampODBC]

Equivalent to typing [FMT:dateODBC] [FMT:timeStampODBC]. This formats the date in yyyy-mm-dd format, and the time in hh:mm:ss format.

[FMT:dateTyped]

Displays the word date and then shows the date, in standard ODBC yyyy-mm-dd format. The date is shown within single quote characters (').

[FMT:timeTyped]

Displays the word time and then shows the time, in standard ODBC hh:mm:ss format. The time is shown within single quote characters (').

[FMT:timeStampTyped]

Displays the word timestamp and then the timestamp, in standard ODBC yyyy-mm-dd hh:mm:ss format. The timestamp is shown within single quote characters (').

Oracle BI Answers Custom Format Strings for Integral Fields

Table 10 shows the custom format strings that are available when working with integral fields. These allow the display of month and day names in the user's locale.

Integral fields hold integers that represent the month of the year or the day of the week. For months, 1 represents January, 2 represents February, and so on, with 12 representing December. For days of the week, 1 represents Sunday, 2 represents Monday, and so on, with 7 representing Saturday.

Table 10. Format Strings for Integral Fields
Integral Field Format String
Result

[MMM]

Displays the abbreviated month name in the user's locale.

[MMMM]

Displays the full month name in the user's locale.

[DDD]

Displays the abbreviated day of the week in the user's locale.

[DDDD]

Displays the full day of the week in the user's locale.

Oracle BI Answers Custom Format Strings for Conversion into Hours

Table 11 shows the custom format strings that can be used to format data into hours. These can be used on the following kinds of fields:

  • Fields that contain integers or real numbers that represent the time that has elapsed since the beginning of the day (12:00 AM).
  • Fields where the output is in [FMT:timeHour] format, described in Table 8. (This format displays the hour field only in the locale's format, such as 8 PM.)
Table 11. Format Strings for Conversion into Hours
Data Conversion Format String
Result

[FMT:timeHour]

This assumes that the value represents the number of hours that have elapsed since the beginning of the day, and formats the number of hours into an hh display, where hh is the number of hours. Fractions are dropped from the value. For example, a value of 2 is formatted as 2 AM, and a value of 12.24 as 12 PM.

[FMT:timeHour(min)]

This assumes that the value represents the number of minutes that have elapsed since the beginning of the day, and formats the number of minutes into an hh display, where hh is the number of hours. Fractions are dropped from the value. For example, a value of 2 is formatted as 12 AM, and a value of 363.10 as 06 AM.

[FMT:timeHour(sec)]

This assumes that the value represents the number of seconds that have elapsed since the beginning of the day, and formats the number of seconds into an hh display, where hh is the number of hours. Fractional hours are dropped from the value. For example, a value of 600 is formatted as 12 AM, a value of 3600 as 1 AM, and a value of a value of 61214.30 as 5 PM.

Oracle BI Answers Custom Format Strings for Conversion into Hours and Minutes

Table 12 shows the custom format strings that can be used to format data into hours and minutes. These can be used on fields that contain integers or real numbers that represent the time that has elapsed since the beginning of the day (12:00 AM).

They can also be used where the output is in [FMT:timeHourMin] format, described in Table 8. (This format displays the time in the locale's time format, but omits the seconds.)

Table 12. Format Strings for Conversion into Hours and Minutes
Data Conversion Format String
Result

[FMT:timeHourMin]

This assumes that the value represents the number of minutes that have elapsed since the beginning of the day, and converts the value into an hh:mm display, where hh is the number of hours and mm is the number of minutes. Fractions are dropped from the value. For example, a value of 12 is formatted as 12:12 AM, a value of 73 as 1:13 AM, and a value of 750 as 12:30 PM.

[FMT:timeHourMin(sec)]

This assumes that the value represents the number of seconds that have elapsed since the beginning of the day, and converts the value into an hh:mm display, where hh is the number of hours and mm is the number of minutes. Fractions are dropped from the value. For example, a value of 60 is formatted as 12:01 AM, a value of 120 as 12:02 AM, and a value of 43200 as 12:00 PM.

[FMT:timeHourMin(hour)]

This assumes that the value represents the number of hours that have elapsed since the beginning of the day, and converts the number of hours into an hh:mm display, where hh is the number of hours and mm is the remaining number of minutes. For example, a value of 0 is formatted as 12:00 AM, a value of 1.5 as 1:30 AM, and a value of 13.75 as 1:45 PM.

Oracle BI Answers Custom Format Strings for Conversion into Hours, Minutes, and Seconds

Table 13 shows the custom format strings that can be used to format data into hours, minutes, and seconds. These can be used on fields that contain integers or real numbers that represent time.

They can also be used where the output is in [FMT:time] format, described in Oracle BI Answers General Custom Format Strings. (This format displays the time in the locale's time format.)

Table 13. Format Strings for Conversion into Hours, Minutes, and Seconds
Data Conversion Format String
Result

[FMT:time]

This assumes that the value represents the number of seconds that have elapsed since the beginning of the day, and converts the value into an hh:mm:ss display, where hh is the number of hours, mm is the number of minutes, and ss is the number of seconds. For example, a value of 60 is formatted as 12:01:00 AM, a value of 126 as 12:02:06 AM, and a value of 43200 as 12:00:00 PM.

[FMT:time(min)]

This assumes that the value represents the number of minutes that have elapsed since the beginning of the day, and converts the value into an hh:mm:ss display, where hh is the number of hours, mm is the number of minutes, and ss is the number of seconds. For example, a value of 60 is formatted as 1:00:00 AM, a value of 126 as 2:06:00 AM, and a value of 1400 as 11:20:00 PM.

[FMT:time(hour)]

This assumes that the value represents the number of hours that have elapsed since the beginning of the day, and converts the value into an hh:mm:ss display, where hh is the number of hours, mm is the number of minutes, and ss is the number of seconds. For example, a value of 6.5 is formatted as 6:30:00 AM, and a value of 12 as 12:00:00 PM.

Oracle® Business Intelligence Answers, Delivers, and Interactive Dashboards User Guide Copyright © 2007, Oracle. All rights reserved.