FORMAT

Format(data, format);

This formula is used to format the way data is inserted in the destination. Often used on Date fields when Rapidi has problems converting automatically or when the date field is transferred to a text field.

Example 1:

##FORMAT("Date",'%Y-%m-%dT%H:%M:%SZ')

If your date field has a format that does not match the destination system and you want the format to be 1900-01-01T00:00:00Z, then you can use the above formula where '%Y-%m-%dT%H:%M:%SZ' consists of: 

  • %Y: 4-digit year.
  • %m: 2-digit month.
  • %d: 2-digit day.
  • T: Literal T to separate date and time.
  • %H: 2-digit hour (24-hour format).
  • %M: 2-digit minute.
  • %S: 2-digit second.
  • Z: Literal Z for UTC indicator.


Example 2: 

##FORMAT("Date", '%d%m%y')

If "Date" has the value "15-05-09" it will return the result "15052009".

 

Example 3:

##FORMAT("Date", '%d.%m.%y')

If "Date" has the value "15-05-2009" it will return the result "15.05.09".

 

Example 4:

FORMAT and CURRENTDATE:

 

##FORMAT(CURRENTDATE(), '%Y%m%d');

 

Here we want to insert a "Current Date" in the destination field when transferring data, but the date format in the destination field should be YYYYMMDD. So, we change the appearance by using the function FORMAT where we add the DateFormatOut specifications %Y%m%d.

%Y inserts year with century as decimal number. %m inserts month as decimal number. %d inserts day of month as decimal.

 

Example 5:

FORMAT and CURRENTDATETIME:

 

##FORMAT(CURRENTDATETIME(), '%Y%m%d%H%M%S')

 

If "CURRENTDATETIME" has the value "15-09-2009 12:44:23" it will return the result "20090915124423".

 


Field Formats

In this section, you will find more information on how to use the Formula "Format".

 

It contains a list of the field formats used for defining the fields within the ASCII Data Source or used for the formula function "FORMAT".


A format specification, which consists of optional and required fields, has the following form:
%[flags] [width] [.precision] [{h | l | I64 | L}]type


Each field of the format specification is a single character or a number signifying a particular format option. The simplest format specification contains only the percent sign and a type character (for example, %s). If a percent sign is followed by a character that has no meaning as a format field, the character is copied to stdout. For example, to print a percent-sign character, use %%.


The required character that determines whether the associated argument is interpreted as a character, a string, or a number is type.


The optional fields which appear before the type character and control other aspects of the formatting are as follows:

  • flags
    Optional character or characters that control justification of output and printing of signs, blanks, decimal points, octal and hexadecimal prefixes. More than one flag can appear in a format specification.
  • width
    Optional number that specifies the minimum number of characters output.
  • precision
    Optional number that specifies the maximum number of characters printed for all or part of the output field, or the minimum number of digits printed for integer values.
  • size and distance specification
    Optional prefixes to type that specify the size of the argument such as {h | l | I64 | L} above.
 

printf Type Field Characters

The type character is the only required format field and it appears after any optional format fields.


The type character determines whether the associated argument is interpreted as a character, string, or number.
The types ?C? and ?S?, and the behavior of ?c? and ?s? with printf functions, are Microsoft extensions and are not ANSI-compatible.

 

Below you will find the Character, Type, and Output Format of printf Type Field Characters:

  • Character c of Type int or wint_t
    When used with printf functions, specifies a single-byte character;
    when used with wprintf functions, specifies a wide character.
  • Character C of Type int or wint_t
    When used with printf functions, specifies a wide character;
    when used with wprintf functions, specifies a single-byte character.
  • Character d of Type int
    Signed decimal integer.
  • Character i of Type int
    Signed decimal integer.
  • Character o of Type int
    Unsigned octal integer.
  • Character u of Type int 
    Unsigned decimal integer.
  • Character x of Type int 
    Unsigned hexadecimal integer, using ?abcdef.?
  • Character X of Type int 
    Unsigned hexadecimal integer, using ?ABCDEF.?
  • Character e of Type double 
    Signed value with the form:
    [ ? ]d.dddd e [sign]ddd where ?d? is a single decimal digit, ?dddd? is one or more decimal digits, ?ddd? is exactly three decimal digits, and sign is + or ?.
  • Character E of Type double 
    Identical to the ?e? format except that ?E? rather than ?e? introduces the exponent.
  • Character f of Type double 
    Signed value having the form:
    [ ? ]dddd.dddd where ?dddd? is one or more decimal digits. The number of digits before the decimal point depends on the magnitude of the number, and the number of digits after the decimal point depends on the requested precision.
  • Character g of Type double
    Signed value printed in ?f? or ?e? format, whichever is more compact for the given value and precision.
    The "e" format is used only when the exponent of the value is less than ?4 or greater than or equal to the precision argument. Trailing zeros are truncated and the decimal point appears only if one or more digits follow it.
  • Character G of Type double 
    Identical to the ?g? format, except that ?E?, rather than ?e?, introduces the exponent (where appropriate).
  • Character n of Type Pointer to integer 
    Number of characters successfully written so far to the stream or buffer; this value is stored in the integer whose address is given as the argument.
  • Character p of Type Pointer to void
    Prints the address pointed to by the argument in the form:
    xxxx:yyyy where ?xxxx? is the segment and ?yyyy? is the offset, and the digits ?x? and ?y? are uppercase hexadecimal digits.
  • Character s of Type String 
    When used with printf functions, specifies a single-byte?character string; 
    when used with wprintf functions, specifies a wide-character string. Characters are printed up to the first null character or until the precision value is reached.
  • Character S of Type String 
    When used with printf functions, specifies a wide-character string; 
    when used with wprintf functions, specifies a single-byte?character string. Characters are printed up to the first null character or until the precision value is reached.
 

Flag Directives

The first optional field of the format specification is flags. A flag directive is a character that justifies output and prints signs, blanks, decimal points, octal and hexadecimal prefixes. More than one flag directive may appear in a format specification.


Flag Characters:

  • Flag: -
    Meaning: Left align the result within the given field width.
    Default: Right align.
  • Flag: +
    Meaning: Prefix the output value with a sign (+ or -) if the output value is of a signed type.
    Default: Sign appears only for negative signed values (-).
  • Flag: 0
    Meaning: If width is prefixed with "0", zeros are added until the minimum width is reached. If "0" and - appear, the "0" is ignored. If "0" is specified with an integer format (i, u, x, X, o, d) the "0" is ignored.
    Default: No padding.
  • Flag: blank (' ')
    Meaning: Prefix the output value with a blank if the output value is signed and positive; the blank is ignored if both the blank and + flags appear.
    Default: No blank appears.
  • Flag :#
    Meaning: When used with the "o", "x" or "X" format, the "#" flag prefixes any nonzero output value with "0", "0x, or "0X" respectively.
    Default: No blank appears.
  • Flag: #
    Meaning: When used with the "e", "E" or "f" format, the # flag forces the output value to contain a decimal point in all cases.
    Default: Decimal point appears only if digits follow it.
  • Flag: #
    Meaning: When used with the "g" or "G" format, the # flag forces the output value to contain a decimal point in all cases and prevents the truncation of trailing zeros.
    Default: Decimal point appears only if digits follw it. Trailing zeros are truncated.
  • Flag: #
    Meaning: Ignored when used with "c", "d", "i", "u" or "s".
 

printf Width Specification

The second optional field of the format specification is the width specification. The width argument is a nonnegative decimal integer controlling the minimum number of characters printed. If the number of characters in the output value is less than the specified width, blank spaces are added to the left or the right of the values, depending on if whether the ? flag (for left alignment) is specified, until the minimum width is reached. If width is prefixed with "0", zeros are added until the minimum width is reached (not useful for left-aligned numbers).

 

The width specification never causes a value to be truncated. If the number of characters in the output value is greater than the specified width, or if the width is not given, all characters of the value are printed (subject to the precision specification).

 

Precision Specification

The third optional field of the format specification is the precision specification. It is a nonnegative decimal integer, preceded by a period (.), which specifies the number of characters to be printed, the number of digits after the decimal point, or the number of significant digits.


Unlike the width specification, the precision specification can cause either truncation of the output value or rounding of a floating-point value. If precision is specified as "0" and the value to be converted is "0", the result is an output of no characters, as shown below:


printf( "%.0d", 0 );      /* No characters output */


If the precision specification is an asterisk (*), an int argument from the argument list supplies the value. The precision argument must precede the value being formatted in the argument list.


The type determines the interpretation of precision and the default when precision is omitted.


The table below lists how type is affected by given precision values:


How Precision Values affect Type:

  • Type: c, C
    Meaning: The precision has no effect.
    Default: Character is printed.
  • Type: d, i, u, o, x, X

  • Meaning: The precision specifies the minimum number of digits to be printed. If the number of digits in the argument is less than precision, the output value is padded on the left with zeros. The value is not truncated when the number of digits exceeds precision. Default: Default precision is 1

  • Type: e, E

  • Meaning: The precision specifies the number of digits to be printed after the decimal point. The last printed digit is rounded. Default: Default precision is "6"; if precision is "0" or the period (.) appears without a number following it, no decimal point is printed.

  • Type: f

  • Meaning: The precision specifies the number of digits after the decimal point. If a decimal point appears, at least one digit appears before it. The value is rounded to the appropriate number of digits. Default: Default precision is "6"; if precision is "0", or if the period (.) appears without a number following it, no decimal point is printed.

  • Type: g, G

  • Meaning: The precision specifies the maximum number of significant digits printed. Default: Six significant digits are printed, with any trailing zeros truncated.

  • Type: s, S

  • Meaning: The precision specifies the maximum number of characters to be printed. Characters in excess of precision are not printed. Default: Characters are printed until a null character is encountered.

 

If the argument corresponding to a floating-point specifier is infinite, indefinite, or NaN, printf gives the following output:

  • Value: + infinity
    Output: 1.#INFrandom-digits
  • Value: ? infinity
    Output: ?1.#INFrandom-digits
  • Value: Indefinite (same as quiet NaN)
    Output: digit.#INDrandom-digits
  • Value: NAN
    Output: digit.#NANrandom-digits
 

Size and Distance Specification

The optional prefixes to type h, l, and L specify the size of the argument (long or short, single-byte character or wide character depending on the type specifier that they modify). These type-specifier prefixes are used with type characters in printf functions or wprintf functions to specify the interpretation of arguments as shown in the following table. These prefixes are Microsoft extensions and are not ANSI-compatible.

 

Size Prefixes for printf and wprintf Format-Type Specifiers:

  • To specify: long int
    Use prefix: l
    With Type Specifier: d, i, o, x, or X
  • To specify: long unsigned int
    Use prefix: l
    With Type Specifier: u
  • To specify: short int
    Use prefix: h
    With Type Specifier: d, i, o, x, or X
  • To specify: short unsigned int
    Use prefix: h
    With Type Specifier: u
  • To specify: _int64
    Use prefix: I64
    With Type Specifier: d, i, o, u, x, or X