Data Conversion Formats

Data Conversion Formats

1.0 Default Data Conversion Formats
2.0 Format Overrides
3.0 Format Override Usage

When data is moved to or from a character field, it is necessary to have a data conversion specifier, also called a format specifier, that describes the conversion parameters.

For char-to-char data moves, the primary format parameter is the target field width. The default target field width is the same as the source field width.

For numeric-to-char data moves, the primary format parameters are the target field width and the number of decimal positions. The default parameters are given in paragraphs 1.1, 1.2 and 1.3 below.

The default mode for converting numeric data to a fixed width character field is to right justify with a trailing sign. Only the '-' sign is expressed. If the value is truncated, the sign field contains '*'.

For example, converting numeric values to a fixed 6 character field gives:

   Numeric     String
   123         '  123 '
   -123        '  123-'
   123456      '23456*'

For DATE, DATETIME, DATETIMEEX and TIME to/from char data moves a template is used. The default templates are given in paragraph 1.4 below. The definition of the template character is given in paragraph 2.2 below.

1.0 Default Data Conversion Formats

1.1 Default Data Conversion Settings for Data Fields by Type
These settings also apply to Calc Object aggregates [MIN] and [MAX]

 
   Field type  Description         width      point
   X(n)        character             n         
   BYTE(n)     binary               n*2+2
   N(n.d)      numeric              n+d+2        d
   I           16 bit int            6         
   IU          16 bit unsigned int   6         
   ID          32 bit int            11        
   IDU         32 bit unsigned int   11        
   ID64        64 bit int            22        
   IDU64       64 bit unsigned int   22        
   ID128       128 bit signed int    35             [Ref Hex Format Descrption]
   F           32 bit float          11          2
   FD          64 bit float          11          2

1.2 Default Settings for Calc Object aggregate [SUM] [AVG] [STD] [VAR] [SSQ]

 
                                        [SUM]          [AVG] [STD] [VAR] [SSQ] 
   Field type  Description         width      point       width      point
   X(n)        character             11                     1
   BYTE(n)     binary                NA                     NA
   N(n.d)      numeric               11          d          1          d
   I           16 bit int            11                     1          2
   IU          16 bit unsigned int   11                     1          2
   ID          32 bit int            11                     1          2
   IDU         32 bit unsigned int   11                     1          2
   ID64        64 bit int            11                     1          2
   IDU64       64 bit unsigned int   11                     1          2
   ID128       128 bit signed int    NA                     NA
   F           32 bit float          11          2          1          2
   FD          64 bit float          11          2          1          2

1.3 Default Settings for Calc Object aggregate [COUNT] [DISTINCT]

 
   Field type           width     
   All Types              11

1.4 Default Templates:

 
   Field type            Template
   DATE                  'mm/dd/yyyy'
   DATETIME              'mm/dd/yyyy hh:nn:ss.ttt'
   DATETIMEEX(0)         'mm/dd/yyyy hh:nn:ss'
   DATETIMEEX+Z(0)       'mm/dd/yyyy hh:nn:ss+hh:nn'
   TIME(0)               'hh:nn:ss'
   DATETIMEEX(n)         'mm/dd/yyyy hh:nn:ss.t(n)'
   DATETIMEEX+Z(n)       'mm/dd/yyyy hh:nn:ss.t(n)+hh:nn'
   TIME(n)               'hh:nn:ss.t(n)'

2.0 Format Overrides

Format overrides are used to override the default data conversion setting. Examples of format override usage are:
   • RD Field Descriptions
   • As a modifier to the ddlCalc() SET command
   • As a modifier to a list item in CalcObject PRINT command
   • As a modifier to a variable substitution

The syntax for a format override for char-to-char and numeric-to-char is:
    ([<modifier>...]<specifier>)

<modifier> and <specifier> are defined below.

2.1 Format overrides for char-to-char and numeric-to-char moves

<specifier> - The basic format specifiers are:

     n         Total field width n; n is a decimal number: 1 <= n <= 4088
     n.d       Total field width n with d fractional digits; n and d are
                  decimal numbers: 1 <=
                       n  < = 
                       
                   4088, 0  < =  d  < = (n-2)

<modifier> - The basic format modifiers are:

     0         Left fill with leading zeroes rather than leading blanks;
                  applies only to numeric-to-char moves;
     +         Use leading sign rather than trailing sign;
                  applies only to numeric-to-char moves;
                  If the '+' modifier is used; and
                     if the source numeric value is non-negative; and
                     if the result numeric string contains one excess digit;
                  Then the additional digit will be stored in the sign field
     ,         Comma edit;
                  applies only to numeric-to-char moves;
     $         Currency edit with $ symbol;
                  applies only to numeric-to-char moves;
     E         Use exponential format unconditionally
     G         Use exponential format if required
     X         Convert to hex;ref Hex Format description;
                  applies to integer data types;
     Z         Display blanks when result is zero;
                  applies only to numeric-to-char moves;
     L         Left justify result;
                  applies to both char-to-char and numeric-to-char moves;
     R         Right justify result;
                  applies to both char-to-char and numeric-to-char moves;
                  however, this is the default fr numeric-to-char moves;
     F         Reverse comma and decimal separators (European style);
                  applies only to numeric-to-char moves;

Examples of format overrides:

     (+n)      Leading sign with total field width n
     ($,n.d)   Currency edit and comma edit

2.2 Format specifiers for DATE/TIME to/from char moves

A template is a string literal that describes the format of a DATE, DATETIME, DateTimeEx or TIME literal.

A template consists of a combination of template characters and non-template characters.

The template characters are:

     Y         a year digit
     M         a month digit
     D         a day digit
     B         an epoch character; recognized epochs are AD, BC and BCE
     H         an hour digit
     N         a minute digit
     S         a second digit
     T         a fractional second digit
     A         an AM/PM character
     +         a timezone offset indicator
     Q         month mapping to quarter; [1-3 ==> 1, 4-6 ==> 2, 7-9 ==> 3, 10-12 ==
                
                
                
                       
                
                
                
                 
                
                 
     >         4] U month truncation to  quarter; [1-3 ==  >  1, 4-6 == 
                > 4, 7-9 ==  >  7, 10-12 ==  >  10]
     X         a month name character
     Z         a day name character

Templates that are used in the context of moving char data to DATE, DATETIME, DateTimeEx or TIME data are called input templates.

A grouping of similar template characters in a template is called a template field. For example, in the template 'mm/dd/yyyy', 'mm', 'dd' and 'yyyy' are template fields.

Templates that are used in the context of moving DATE, DATETIME, DateTimeEx or TIME data to char date are called output templates.

2.3 Hex Format Output

Beginning with TB API Version 8.2.1, integer data can also be converted to hexadecimal format. This is the default conversion for ID128 data type and is optional for all other integer data types.

All ID128 data conversions default to (X35) format. At present, there is no option for decimal conversion.

The output conversion for all other integer data types can optionally specify hex conversion with the (X) format override.

The (X) can be used with or without a width specifier. In either case, the width is coerced to the minumum width as defined below:

 
   Field type  Description                minimum width
   I           16 bit signed integer      7
   IU          16 bit unsigned integer    7
   ID          32 bit signed integer      11
   IDU         32 bit unsigned integer    11
   ID64        64 bit signed integer      19
   IDU64       64 bit unsigned integer    19
   ID128       128 bit integer            35

Hex output ignores all other format override options.

The default and only character output format for BYTE data type is also hex.

2.4 Hex Format Input

Except for data type ID128, input for integer data types can optionally be specified in hex format. For data type ID128 hex format is the default and only format for data input.

Hex data input does not require a special format override specifier. Rather, the hex data itself must be precede by the '0x' prefix.

The default and only character input format for BYTE data type is also hex.

3.0 Format Override Usage

3.1 DBD and RD Field Definition

3.2 SET <char-variable> = <char-value>

3.3 SET <char-variable> = <numeric-value>

3.4 Calc Object PRINT

3.5 ddlWrite(), ddlChange(), ddlFetch()

3.6 ddlFind()

3.7 Variable Substitution

The term variable substitution refers to the process of substituting the value of a variable into a command string. The use of variable substitution within TB API functions is limited. But variable substitution within DDTL commands is universal.

The syntax for performing variable substitution is:
       {%Variable}
       {%Variable(<format>)}

Examples of variable substitution (in DDTL notation) are:
       SET %Name = 'George'
       TYPE Hello {%name}
       Produces:
             Hello George

       CALC %Value = 12345
       TYPE Value is: {%Value}
       Produces:
             Value: 12345.00

For string variable substitution, the only valid format specifier is n, the field width. For example:
       TYPE Hello {%name(3)}
       Produces:
             Hello Geo

For numeric variable substitution, the only valid specifiers are:

     .d        Number of fractional digits
     ,         Comma edit;

For example:
       TYPE Value is: {%Value(,.0)}
       Produces:
             Value: 12,345