How to: Reference: |
When you add transformations using either the Source or Target Transformations window, you can assign formats to the columns that you are adding. There are four types of column formats:
For more information about numeric formats, see Numeric Format Options.
A column described as BLOB can only be copied from source to target. No transformations can be performed.
Columns that you map to each other must have the same format, or DataMigrator must be able to translate the source query format into the target format automatically. Details are shown in the following table:
If the Target format is... |
...and the Source query format is... |
Can you map? |
---|---|---|
A11 |
A10 |
Yes |
A10 |
A11 |
No. Use the EDIT or SUBSTR functions to make this work. |
D6.2 |
I5 |
Yes |
I10 |
D6.2 |
Yes, but you will lose the numbers after the decimal. |
A10 |
D6.2 |
No |
If you are creating a new or virtual column, you need to assign a format to the column. You can assign a format in two ways:
To use the Define Properties dialog box:
The Define Properties dialog box opens.
To access the Define Properties dialog box from the Transformation Calculator, click the ellipsis (...) button to the right of the Format field.
The Define Properties dialog box has the following fields and options:
The format name of the column.
The format type of the column.
Note: The options available will depend on the type of format selected.
Indicates if the results can be missing. The following options are available:
When using a numeric format (Integer, Float, Double, or Decimal Packed), the following options are available:
These are the display options for negative numbers:
Positions the minus (-) sign at the left of the number.
Positions the minus (-) sign at the right of the number.
Encloses a negative number in parentheses.
Adds the characters CR after a negative number.
These are the display options for commas:
Suppresses the use of a comma in the number.
Suppresses the use of a comma in the number.
Inserts a comma after every third significant figure. This option takes effect automatically for Double format.
Adds leading zeros to the full field length.
Suppresses leading zeros; displays a space if the value is 0.
Displays a percent sign along with the numeric data.
Displays the number in scientific notation.
These are the extended currency symbol display options, which allow you to select a currency symbol regardless of what is set as the default:
Specifies a field as a currency denominated value.
The Define Properties dialog box for the Double format is shown in the image below:
The Define Properties dialog box opens.
The Date Format allows you to choose the display for dates (YYMD, MDY, and so on).
The Time Format allows you to choose the display for time (seconds, milliseconds, and so on) and am/pm.
The DateTime Format allows you to choose the display for both date and time portions.
The following choices are available from the Define Properties dialog box for the date format (the examples illustrate August 21, 2004):
Format |
Description |
Example |
---|---|---|
YYMD |
Four-digit Year/Month/Day. |
2004/08/21 |
MDYY |
Month/Day/Four-digit Year. |
08/21/2004 |
DMYY |
Day/Month/Four-digit Year. |
21/08/2004 |
YYQ |
Four-digit Year/Quarter |
2004 Q4 |
QYY |
Quarter Four-digit Year. |
Q4 2004 |
YYM |
Four-digit Year/Month. |
2004/08 |
MYY |
Month/Four-digit Year. |
08/2004 |
YYJUL |
Four-digit Year with the month in Julian format. The last three digits are the number of days since January 1. |
2004/234 |
YY |
Four-digit year. |
2004 |
Y |
Two-digit year. |
04 |
Q |
Quarter. |
Q3 |
M |
Month. |
08 |
MT |
Short month in uppercase. |
AUG |
Mt |
Short month in mixed-case. |
Aug |
MTR |
Full month in uppercase. |
AUGUST |
Mtr |
Full month in mixed-case. |
August |
W |
Three-letter day of the week abbreviation in uppercase. |
SAT |
w |
Three-letter day of the week abbreviation in mixed-case. |
Sat |
WR |
Day of the week in uppercase. |
SATURDAY |
wr |
Day of the week in mixed-case. |
Saturday |
D |
Day. |
21 |
The following choices are available from the Define Properties dialog box for the time format (the example illustrates 2:05 a.m.):
Format |
Description |
Example |
---|---|---|
HH |
Hours. |
02 |
HHI |
Hours/Minutes. |
02:05 |
HHIS |
Hours/Minutes/Seconds. |
02:05:27 |
HHISs |
Hours/Minutes/Seconds/Milliseconds. |
02:05:27:123 |
HHISsm |
Hours/Minutes/Seconds/Milliseconds/Microseconds. |
02:05:27:123456 |
HI |
Minutes. |
05 |
HIS |
Minutes/Seconds. |
05:27 |
HISs |
Minutes/Seconds/Milliseconds. |
05:27:123 |
HISsm |
Minutes/Seconds/Milliseconds/Microseconds. |
05:27:123456 |
HS |
Seconds |
27 |
HSs |
Seconds/Milliseconds. |
27:123 |
HSsm |
Seconds/Milliseconds/Microseconds. |
27:123456 |
Hh |
Hours without leading zeros. |
2 |
HhI |
Hours without leading zeros/Minutes. |
2:05 |
HhIS |
Hours without leading zeros/Minutes/Seconds. |
2:05:27 |
HhISs |
Hours without leading zeros/Minutes/Seconds/Milliseconds. |
2:05:27:123 |
HhISsm |
Hours without leading zeros/Minutes/Seconds/Milliseconds/Microseconds. |
2:05:27:123456 |
Hi |
Minutes without leading zeros. |
5 |
HiS |
Minutes without leading zeros/Seconds. |
5:27 |
HiSs |
Minutes without leading zeros/Seconds/Milliseconds. |
5:27:123 |
HiSsm |
Minutes without leading zeros/Seconds/Milliseconds/Microseconds. |
5:27:123456 |
The DateTime(Timestamp) type format combines date, time, and a separator, which are reflected in the format name. For example, HYYMD-H describes a four-digit year, month, and day separated by dashes plus hours.
The following choices are available from the Define Properties dialog box for the date component:
Format |
Description |
Example |
---|---|---|
HYYMD |
Four-digit Year/Month/Day. |
2004/08/21 |
HYMD |
Two-digit Year/Month. |
04/08 |
HMD |
Month/Day. |
08/21 |
HD |
Day |
21 |
The time component is added to the date format name as follows:
Format |
Description |
Format Name |
Example |
---|---|---|---|
H |
Hours. |
HYYMDH |
2004/08/21 02 |
I |
Hours/Minutes. |
HYYMDI |
2004/08/21 02:05 |
S |
Hours/Minutes/Seconds. |
HYYMDS |
2004/08/21 02:05:27 |
s |
Hours/Minutes/Seconds/Milliseconds. |
HYYMDs |
2004/08/21 02:05:27:123 |
m |
Hours/Minutes/Seconds/ Milliseconds/Microseconds. |
HYYMDm |
2004/08/21 02:05:27:123456 |
The following choices are available from the Define Properties dialog box for the separator, which is added to the format name.
Format |
Description |
Format Name |
Example |
---|---|---|---|
/ |
Slash. |
HYYMDH |
2004/08/21 02 |
. |
Period. |
HYYMD.H |
2004.08.21 |
- |
Dash. |
HYYMD-H |
2004-08-21 |
Blank |
Space |
HYYMDBH |
2004 08 21 |
None |
No separator |
HYYMDNH |
20040821 |
U |
International default. |
HYYMDU |
iWay Software |