Custom Format Strings

Custom format strings provide additional options for formatting columns or hierarchy levels that contain time stamps, dates, and times. To set custom format strings to a column or hierarchy level, you use the Custom Date Format field (for date and time fields) or the Custom Numeric Format field (for numeric fields) in the "Column Properties dialog: Data Format tab".

This section contains the following topics:

"Custom Format Strings for Date and Time Fields"
"Custom Format Strings for Numeric Fields"

Custom Format Strings for Date and Time Fields

You can set custom format strings to a column or hierarchy level in the Custom Date Format field in the "Column Properties dialog: Data Format tab".

This section contains the following topics:

"General Custom Format Strings"
"ODBC Custom Format Strings"

General Custom Format Strings

Table 7-2 shows the general custom format strings and the results that they display. These allow the display of date and time fields in the user's locale.

Table 7-2 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]. 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]. 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]. 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.
YY or yy	Displays the last two digits of the year, padded to the left with zero, if necessary, for example, 01 for 2001.
YYY or yyy	Displays the four- digit year, padded to the left with zero, if necessary, for example, 0523.
YYYY or yyyy	Displays the four- digit year, for example, 2011
M	Displays the numeric month, for example, 2 for February.
MM	Displays the numeric month, padded to the left with zero for single-digit months, for example, 02 for February.
MMM	Displays the abbreviated name of the month in the user's locale, for example, Feb.
MMMM	Displays the full name of the month in the user's locale, for example, February.
D or d	Displays the day of the month, for example, 1.
DD or dd	Displays the day of the month, padded to the left with zero for single-digit days, for example, 01.
DDD or ddd	Displays the abbreviated name of the day of the week in the user's locale, for example, Thu for Thursday.
DDDD or dddd	Displays the full name of the day of the week in the user's locale, for example, Thursday.
DDDDD or ddddd	Displays the first letter of the name of the day of the week in the user's locale, for example, T for Thursday.
h	Displays the hour in 12-hour time, for example 2.
H	Displays the hour in 24-hour time, for example, 23.
hh	Displays the hour in 12-hour time, padded to the left with zero for single-digit hours, for example, 01.
HH	Displays the hour in 24-hour time, padded to the left with zero for single digit hours, for example, 23.
m	Displays the minute, for example, 7.
mm	Displays the minute, padded to the left with zero for single-digit minutes, for example, 07.
s	Displays the second, for example, 2. You can also include decimals in the string, such as s.# or s.00 (where # means an optional digit, and 0 means a required digit).
ss	Displays the second, padded to the left with zero for single-digit seconds, for example, 02. You can also include decimals in the string, such as ss.# or ss.00 (where # means an optional digit, and 0 means a required digit).
t	Displays the first letter of the abbreviation for ante meridiem or post meridiem in the user's locale, for example, a.
tt	Displays the abbreviation for ante meridiem or post meridiem in the user's locale, for example, pm.
gg	Displays the era in the user's locale.

ODBC Custom Format Strings

Table 7-3 shows the ODBC standard type custom format strings and the results that they display. These strings display date and time fields according to the ODBC standard.

Table 7-3 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]. 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 (').

Custom Format Strings for Numeric Fields

You can set custom format strings to a column or hierarchy level in the Custom Numeric Format field in the "Column Properties dialog: Data Format tab".

This section contains the following topics:

"Custom Format Strings for Integral Fields"
"Custom Format Strings for Conversion into Hours"
"Custom Format Strings for Conversion into Hours and Minutes"
"Custom Format Strings for Conversion into Hours, Minutes, and Seconds"
"Custom Format Strings for Displaying Duration"

Custom Format Strings for Integral Fields

Table 7-4 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 7-4 Format Strings for Integral Fields

Integral Field Format String	Result
[MMM]	Displays the abbreviated name of the month in the user's locale.
[MMMM]	Displays the full name of the month in the user's locale.
[DDD]	Displays the abbreviated name of the day of the week in the user's locale.
[DDDD]	Displays the full name of the day of the week in the user's locale.

Custom Format Strings for Conversion into Hours

Table 7-5 shows the custom format strings that you can use to format data into hours. You use these strings 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, as described in Table 7-2, "General Custom Format Strings". (This format displays the hour field only in the locale's format, such as 8 PM.)

Table 7-5 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.

Custom Format Strings for Conversion into Hours and Minutes

Table 7-6 shows the custom format strings that you can use to format data into hours and minutes. You use these strings 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 7-2, "General Custom Format Strings". (This format displays the time in the locale's time format, but omits the seconds.)

Table 7-6 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.

Custom Format Strings for Conversion into Hours, Minutes, and Seconds

Table 7-7 shows the custom format strings that you can use to format data into hours, minutes, and seconds. You use these strings 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 "General Custom Format Strings". (This format displays the time in the locale's time format.)

Table 7-7 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.

Custom Format Strings for Displaying Duration

Table 7-8 shows the custom format strings that you can use to format duration data into days, hours, minutes, and seconds. You use these strings on fields that contain integers or real numbers that represent time.

This allows the display of duration, such as the length of time for a telephone call. You can format a numeric field as DD:HH:MM:SS.

You can add literal values in the string to make the output more meaningful, as in the following example:

[duration][opt:dd"d":][opt:hh"h":]mm"m":ss"s"
When input = 15000, output is: 04h:10m:00s

In this way, the literal values h, m, and s represent hours, minutes, and seconds in the output.

Adding [opt:...] anywhere in the string makes the field optional, in that it is not displayed if it has no value. In the example above, [opt:dd] means a value for days is not displayed because it has no value.

Table 7-8 Format Strings for Displaying Duration

Data Conversion Format String	Result
[duration(sec)][opt:dd]:hh:mm:ss	Formats the total of seconds as duration. For example, a duration value of 16500.00 is displayed as 04:35:00, meaning a duration of four hours, 35 minutes, and zero seconds. opt:dd displays the number of days, but if opt has no value, it is not displayed. Second is the default unit of time.
[duration(min)][opt:dd]:hh:mm:ss	Formats the total of minutes as duration. For example, a duration value of 275.00 is displayed as 04:35:00, meaning a duration of four hours, 35 minutes, and zero seconds. opt:dd displays the number of days, but if opt has no value, it is not displayed.
[duration(hour)][opt:dd]:hh:mm:ss	Formats the total of hours as duration. For example, a duration value of 4.58 is displayed as 04:35:00, meaning a duration of four hours, 34 minutes, and 48 seconds. opt:dd displays the number of days, but if opt has no value, it is not displayed.
[duration(day)][opt:dd]:hh:mm:ss	Formats the total of days as duration. For example, a duration value of 2.13 is displayed as 02:03:07:12, meaning a duration of two days, three hours, seven minutes, and twelve seconds. opt:dd displays the number of days, but if opt has no value, it is not displayed.