Teradata Package for Python Function Reference | 20.00 - format - Teradata Package for Python - Look here for syntax, methods and examples for the functions included in the Teradata Package for Python.
Teradata® Package for Python Function Reference - 20.00
- Deployment
- VantageCloud
- VantageCore
- Edition
- Enterprise
- IntelliFlex
- VMware
- Product
- Teradata Package for Python
- Release Number
- 20.00.00.03
- Published
- December 2024
- ft:locale
- en-US
- ft:lastEdition
- 2024-12-19
- dita:id
- TeradataPython_FxRef_Enterprise_2000
- lifecycle
- latest
- Product Category
- Teradata Vantage
- teradataml.dataframe.sql.DataFrameColumn.format = format(self, formatter)
- DESCRIPTION:
Function to format the values in column based on formatter.
PARAMETERS:
formatter:
Required Argument.
Specifies a variable length string containing formatting characters
that define the display format for the data type.
Formats can be specified for columns that have character, numeric, byte,
DateTime, Period or UDT data types.
Note:
If the "formatter" does not include a sign character or a signed zoned decimal character,
then the sign for a negative value is discarded and the output is displayed as a positive
number.
Types: str
Following tables provide information about different formatters on numeric and
date/time type columns:
The formatting characters determine whether the output of numeric data is considered
to be monetary or non-monetary. Numeric information is considered to be monetary if the
"formatter" contains a currency symbol.
Note:
Formatting characters are case insensitive.
The result of a formatted numeric string is right-justified.
+--------------------------------------------------------------------------------------------------+
| formatter description |
+--------------------------------------------------------------------------------------------------+
| G Invokes the currency grouping rule defined by CurrencyGroupingRule in the SDF. |
| The value of CurrencyGroupSeparator in the current SDF is copied to the output |
| string to separate groups of digits to the left of the currency radix separator|
| ,according to the currency grouping rule defined by CurrencyGroupingRule. |
| Grouping applies to only the integer portion of floating numbers. |
| The G must appear as the first character in a "formatter". |
| A currency character, such as L or C, must also appear in the "formatter". |
| If the "formatter" does not contain the letter G, no grouping is |
| done on the output string. |
| The G cannot appear in a "formatter" that contains any of the following |
| characters: |
| * , |
| * . |
| * / |
| * : |
| * S |
| Examples: |
| +---------------------------------------------------------------+ |
| | data formatter result | |
| +---------------------------------------------------------------+ |
| | -12345678.90 'G--(8)D9(2)' -12,345,678.90 | |
| | 1234567890 'G-(10)9' 1.234.567.890 | |
| | 9988.77 'GLLZ(I)D9(F)' $9,988.77 | |
| +---------------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| |
| / Insertion characters. |
| |
| : Copied to output string where they appear in the "formatter". |
| |
| % The % insertion character cannot appear in a "formatter" that contains S, |
| and cannot appear between digits in a "formatter" that contains G, D, or E. |
| For example, GL9999D99% is valid, but L9(9)D99%E999 is not. |
| |
| The / and : insertion characters cannot appear in a "formatter" that |
| contains any of the following characters: |
| * G |
| * D |
| * S |
| Examples: |
| +---------------------------------------------------------------+ |
| | data formatter result | |
| +---------------------------------------------------------------+ |
| | 1234567891 '9(8)/9(2)' 12345678/91 | |
| | 1234567891 '9(8):9(2)' 12345678:91 | |
| | 1234567891 '9(8)%9(2)' 12345678%91 | |
| +---------------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| |
| B Insertion character. |
| A blank space is copied to the output string wherever a B appears in the FORMAT |
| phrase. |
| B cannot appear between digits in a "formatter" that contains G, D, or E. |
| For example, GNB99D99 is valid, but G9(9)BD99 is not. |
| Examples: |
| +---------------------------------------------------------------+ |
| | data formatter result | |
| +---------------------------------------------------------------+ |
| | 998877.66 '-Z(I)BN' 998878 US Dollars | |
| +---------------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| |
| + Sign characters. |
| |
| - These characters can appear at the beginning or end of a format string, |
| but cannot appear between Z or 9 characters, or between repeated currency |
| characters. One sign character places the edit character in a fixed position |
| for the output string. |
| If two or more of these characters are present, the sign floats (moves to the |
| position just to the left of the number as determined by the stated structure). |
| Repeated sign characters must appear to the left of formatting characters |
| consisting of a combination of the radix and any 9 formatting characters. |
| |
| If a group of repeated sign characters appears in a "formatter" with a group |
| of repeated Z characters or a group of repeated currency characters or both, the|
| groups must be contiguous. For example, +++$$$ZZZ. |
| One trailing sign character can occur to the right of any digits, and can |
| combine with B and one currency character or currency sign. For example, |
| G9(I)B+L. The trailing sign character for a mantissa cannot appear to the right |
| of the exponent. |
| For example, 999D999E+999+ is invalid. |
| |
| The + translates to + or - as appropriate; the - translates to - or blank. |
| Examples: |
| +---------------------------------------------------------------+ |
| | data formatter result | |
| +---------------------------------------------------------------+ |
| | 0034567890 '--(8)D9(2)' 34567890.00 | |
| | -0034567890 '++(8)D9(2)' -34567890.00 | |
| +---------------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| $ Currency signs: |
| * $ means Dollar sign. |
| £ * £ means Pound sterling. |
| * ¥ means Yen sign. |
| ¥ * ¤ means general currency sign. |
| |
| ¤ A currency sign cannot appear between Z or 9 formatting characters, or between |
| repeated sign characters. |
| One currency sign places the edit character in a fixed position for the output |
| string. |
| If a result is formatted using a single currency sign with Zs for |
| zero-suppressed decimal digits (for example, £ZZ9.99), blanks can occur between |
| the currency sign and the leftmost nonzero digit of the number. |
| |
| If the same currency sign appears more than once, the sign floats to the right, |
| leaving no blanks between it and the leftmost digit. |
| |
| A currency sign cannot appear in the same phrase with a currency character, |
| such as L. |
| If + or - is present, the currency character cannot precede it. |
| |
| If a group of repeated currency signs appears in a "formatter" with a group of |
| repeated sign characters or a group of repeated Z characters or both, the groups|
| must be contiguous. For example, +++$$$ZZZ. |
| |
| One currency sign can occur to the right of any digits, and can combine with B |
| and one trailing sign character. For example, G9(I)B+$. |
| |
| A currency sign cannot appear to the right of an exponent. For example, |
| 999D999E+999B+$ is invalid. |
| Examples: |
| +---------------------------------------------------------------+ |
| | data formatter result | |
| +---------------------------------------------------------------+ |
| | 1 '$(5).9(2)' $1.00 | |
| | .069 '$$9.99' $0.07 | |
| | 1095 '$$9.99' ****** | |
| +---------------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| |
| L Currency characters. |
| The value of the corresponding currency string in the current SDF is copied to |
| C the output string whenever the character appears in the "formatter". |
| * L in a "formatter" is interpreted as the currency symbol and the value |
| N of the Currency string in the SDF is copied to the output string. |
| * C in a "formatter" is interpreted as the ISO currency symbol and the |
| O value of the ISOCurrency string in the SDF is copied to the output |
| string. |
| * N in a "formatter" is interpreted as the full currency name, such as Yen |
| U or Kroner, and the value of the CurrencyName string in the SDF is copied |
| to the output string. |
| A * O in a "formatter" is interpreted as the dual currency symbol and the |
| value of the DualCurrency string in the SDF is copied to the output |
| string. |
| * U in a "formatter" is interpreted as the dual ISO currency symbol and |
| the value of the DualISOCurrency string in the SDF is copied to the |
| output string. |
| * A in a "formatter" is interpreted as the full dual currency name, |
| such as Euro, and the value of the DualCurrencyName string in the SDF |
| is copied to the output string. |
| |
| A currency character cannot appear between Z or 9 formatting characters, or |
| between repeated sign characters. |
| If the same currency character appears more than once, the value that is |
| copied to the output string floats to the right, leaving no blanks between it |
| and the leftmost digit. Repeated characters must be contiguous, and must appear |
| to the left formatting characters consisting of a combination of the radix and |
| any 9 formatting characters. |
| |
| If a group of repeated currency characters appears in a "formatter" with a |
| group of repeated sign characters or a group of repeated Z characters or both, |
| the groups must be contiguous. For example, +++LLLZZZ. |
| A currency character cannot appear in the same phrase with any of the following |
| characters: |
| * other currency characters |
| * a currency sign, such as $ or £ |
| * , |
| * . |
| One currency character can occur to the right of any digits, and can combine |
| with B and one trailing sign character. For example, G9(I)B+L. |
| |
| A currency character cannot appear to the right of an exponent. For example, |
| 999D999E+999B+L is invalid. |
| Examples: |
| +----------------------------------------------------------------+ |
| | data formatter result | |
| +----------------------------------------------------------------+ |
| | 9988.77 'GLLZ(I)D9(F)' $9,988.77 | |
| | 9988.77 'GCBZ(4)D9(F)' USD 9,988.77 | |
| | 9988.77 'GNBZ(4)D9(F)' US Dollars 9,988.77| |
| +----------------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| |
| V Implied decimal point position. |
| Internally, the V is recognized as a decimal point to align the numeric value |
| properly for calculation. |
| Because the decimal point is implied, it does not occupy any space in storage |
| and is not included in the output. |
| V cannot appear in a "formatter" that contains the 'D' radix symbol or |
| the '.' radix character. |
| Examples: |
| +---------------------------------------------------------------+ |
| | data formatter result | |
| +---------------------------------------------------------------+ |
| | 128.457 '999V99' 12846 | |
| +---------------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| |
| Z Zero-suppressed decimal digit. |
| Translates to blank if the digit is zero and preceding digits are also zero. |
| |
| A Z cannot follow a 9. |
| |
| Repeated Z characters must appear to the left of any combination of the radix |
| and any 9 formatting characters. |
| |
| The characters to the right of the radix cannot be a combination of 9 and Z |
| characters; they must be all 9s or all Zs. If they are all Zs, then the |
| characters to the left of the radix must also be all Zs. |
| |
| If a group of repeated Z characters appears in a "formatter" with a group of |
| repeated sign characters, the group of Z characters must immediately follow |
| the group of sign characters. For example, --ZZZ. |
| Examples: |
| +---------------------------------------------------------------+ |
| | data formatter result | |
| +---------------------------------------------------------------+ |
| | 1.3451 'zz.z' 1.3 | |
| +---------------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| |
| 9 Decimal digit (no zero suppress). |
| Examples: |
| +---------------------------------------------------------------+ |
| | data formatter result | |
| +---------------------------------------------------------------+ |
| | 0034567890 '9999999999' 0034567890 | |
| +---------------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| |
| E For exponential notation. |
| |
| Defines the end of the mantissa and the start of the exponent. |
| |
| The exponent consists of one optional + or - sign character followed by one or |
| more 9 formatting characters. |
| Examples: |
| +-------------------------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------------------------+ |
| |1095 '9.99E99' 1.10E03 | |
| |1.74524064372835e-2 '-9D99999999999999E-999' 1.74524064372835E-002| |
| +-------------------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| |
| CHAR(n) For more than one occurrence of a character, where CHAR can be one of the |
| following: |
| * - (sign character) |
| * + |
| * Z |
| * 9 |
| * $ |
| * ¤ |
| * ¥ |
| * £ |
| and n can be: |
| * an integer constant |
| * I |
| * F |
| If n is F, CHAR can only be Z or 9. |
| |
| If n is an integer constant, the (n) notation means that the character repeats n|
| number of times. For the meanings of I and F, see the definitions later in this |
| table. |
| Examples: |
| +---------------------------------------------------------------+ |
| | data formatter result | |
| +---------------------------------------------------------------+ |
| | 0034567890 'z(8)D9(2)' 34567890.00 | |
| | -0034567890 '+z(8)D9(2)' -34567890.00 | |
| +---------------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| |
| , Currency grouping character. |
| The comma is inserted only if a digit has already appeared. |
| |
| The comma is interpreted as the currency grouping character regardless of the |
| value of the CurrencyGroupSeparator in the SDF. |
| |
| The comma cannot appear in a "formatter" that contains any of the following |
| characters: |
| * G |
| * D |
| * L |
| * C |
| * O |
| * U |
| * N |
| * A |
| * S |
| Examples: |
| +---------------------------------------------------------------+ |
| | data formatter result | |
| +---------------------------------------------------------------+ |
| | 0034567890 'z(7),z' 3456789,0 | |
| +---------------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| |
| . Currency radix character. |
| The period is interpreted as the currency radix character, regardless of the |
| value of the CurrencyRadixSeparator in the SDF, and is copied to the output |
| string. |
| |
| The period cannot appear in a "formatter" that contains any of the following |
| characters: |
| * G |
| * D |
| * L |
| * V |
| * C |
| * O |
| * U |
| * N |
| * A |
| * S |
| Examples: |
| +---------------------------------------------------------------+ |
| | data formatter result | |
| +---------------------------------------------------------------+ |
| | 0034567890 'z(8).z' 34567890.0 | |
| +---------------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| |
| D Radix symbol. |
| The value of CurrencyRadixSeparator in the current SDF is copied to the output |
| string whenever a D appears in the "formatter". |
| A currency symbol, such as a dollar sign or yen sign, must also appear in the |
| "formatter". |
| The D cannot appear in a "formatter" that contains any of the following |
| characters: |
| * , |
| * . |
| * / |
| * : |
| * S |
| * V |
| Examples: |
| +---------------------------------------------------------------+ |
| | data formatter result | |
| +---------------------------------------------------------------+ |
| | 0034567890 '--(8)D9(2)' 34567890.00 | |
| +---------------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| |
| I The number of characters needed to display the integer portion of numeric and |
| integer data. |
| |
| I can only appear as n in the CHAR(n) character sequence (see the definition of |
| CHAR(n) earlier in this table), where CHAR can be: |
| * - (sign character) |
| * + |
| * Z |
| * 9 |
| * $ |
| * ¤ |
| * ¥ |
| * £ |
| CHAR(I) can only appear once, and is valid for the following types: |
| * DECIMAL/NUMERIC |
| * BYTEINT |
| * SMALLINT |
| * INTEGER |
| * BIGINT |
| The value of I is resolved during the formatting of the monetary numeric data. |
| The value is obtained from the declaration of the data type. For example, I is |
| eight for the DECIMAL(10,2) type. |
| |
| If CHAR(F) also appears in the "formatter", CHAR(F) must appear to the right of |
| CHAR(I), and one of the following characters must appear between CHAR(I) and |
| CHAR(F): |
| * D |
| * . |
| * V |
| Examples: |
| +---------------------------------------------------------------+ |
| | data formatter result | |
| +---------------------------------------------------------------+ |
| | 000000.42 'Z(I)D9(F)' .42 | |
| +---------------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| F The number of characters needed to display the fractional portion of |
| numeric data. |
| F can only appear as n in the CHAR(n) character sequence |
| (see the definition of CHAR(n) earlier in this table), |
| where CHAR can be: |
| * Z |
| * 9 |
| CHAR(F) is valid for the DECIMAL/NUMERIC data type. |
| |
| The value of F is resolved during the formatting of the monetary numeric data. |
| The value is obtained from the declaration of the data type. For example, |
| F is two for the DECIMAL(10,2) type. |
| |
| A value of zero for F displays no fractional precision for the data; however, |
| the value of CurrencyRadixSeparator in the current SDF is copied to the output |
| string if a D appears in the "formatter". |
| |
| CHAR(F) can appear only once. If CHAR(I) also appears in the "formatter", |
| CHAR(F) must appear to the right of CHAR(I), and one of the following characters|
| must appear between CHAR(I) and CHAR(F): |
| * D |
| * . |
| * V |
| Examples: |
| +---------------------------------------------------------------+ |
| | data formatter result | |
| +---------------------------------------------------------------+ |
| | 000000.42 'Z(I)D9(F)' .42 | |
| +---------------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| |
| - Dash character. |
| Used when storing numbers such as telephone numbers, social security numbers, |
| and account numbers. |
| |
| A dash appears after the first digit and before the last digit, and is treated |
| as an embedded dash rather than a sign character. A dash cannot follow any of |
| these characters: |
| * . |
| * , |
| * + |
| * G |
| * N |
| * A |
| * C |
| * L |
| * O |
| * U |
| * D |
| * V |
| * S |
| * E |
| * $ |
| * ¤ |
| * £ |
| * ¥ |
| Examples: |
| +---------------------------------------------------------------+ |
| | data formatter result | |
| +---------------------------------------------------------------+ |
| | 0034567890 '--(8)D9(2)' 34567890.00 | |
| +---------------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| |
| S Signed Zoned Decimal character. |
| Defines signed zoned decimal input as a numeric data type and displays numeric |
| output as signed zone decimal character strings. |
| |
| When converting signed zone decimal input to a numeric data type, the final |
| character is converted as follows: |
| * Last character = { or 0, then the numeric conversion is n … 0 |
| * Last character = A or 1, then the numeric conversion is n … 1 |
| * Last character = B or 2, then the numeric conversion is n … 2 |
| * Last character = C or 3, then the numeric conversion is n … 3 |
| * Last character = D or 4, then the numeric conversion is n … 4 |
| * Last character = E or 5, then the numeric conversion is n … 5 |
| * Last character = F or 6, then the numeric conversion is n … 6 |
| * Last character = G or 7, then the numeric conversion is n … 7 |
| * Last character = H or 8, then the numeric conversion is n … 8 |
| * Last character = I or 9, then the numeric conversion is n … 9 |
| * Last character = }, then the numeric conversion is -n … 0 |
| * Last character = J, then the numeric conversion is -n … 1 |
| * Last character = K, then the numeric conversion is -n … 2 |
| * Last character = L, then the numeric conversion is -n … 3 |
| * Last character = M, then the numeric conversion is -n … 4 |
| * Last character = N, then the numeric conversion is -n … 5 |
| * Last character = O, then the numeric conversion is -n … 6 |
| * Last character = P, then the numeric conversion is -n … 7 |
| * Last character = Q, then the numeric conversion is -n … 8 |
| * Last character = R, then the numeric conversion is -n … 9 |
| |
| When displaying numeric output as signed zone decimal character strings, |
| the final character indicates the sign, as follows: |
| |
| If the final data digit is 0, then the final result digit is displayed as: |
| * { if the result is a positive number |
| * } if the result is a negative number |
| If the final data digit is 1, then the final result digit is displayed as: |
| * A if the result is a positive number |
| * J if the result is a negative number |
| If the final data digit is 2, then the final result digit is displayed as: |
| * B if the result is a positive number |
| * K if the result is a negative number |
| If the final data digit is 3, then the final result digit is displayed as: |
| * C if the result is a positive number |
| * L if the result is a negative number |
| If the final data digit is 4, then the final result digit is displayed as: |
| * D if the result is a positive number |
| * M if the result is a negative number |
| If the final data digit is 5, then the final result digit is displayed as: |
| * E if the result is a positive number |
| * N if the result is a negative number |
| If the final data digit is 6, then the final result digit is displayed as: |
| * F if the result is a positive number |
| * O if the result is a negative number |
| If the final data digit is 7, then the final result digit is displayed as: |
| * G if the result is a positive number |
| * P if the result is a negative number |
| If the final data digit is 8, then the final result digit is displayed as: |
| * H if the result is a positive number |
| * Q if the result is a negative number |
| If the final data digit is 9, then the final result digit is displayed as: |
| * I if the result is a positive number |
| * R if the result is a negative number |
| |
| The S must follow the last decimal digit in the "formatter". It cannot appear |
| in the same phrase with the following characters. |
| * % |
| * + |
| * : |
| * / |
| * - |
| * , |
| * . |
| * Z |
| * E |
| * D |
| * G |
| * F |
| * N |
| * A |
| * C |
| * L |
| * U |
| * O |
| * $ |
| * ¤ |
| * £ |
| * ¥ |
| Examples: |
| +---------------------------------------------------------------+ |
| | data formatter result | |
| +---------------------------------------------------------------+ |
| | -1095 '99999S' 0109N | |
| | 1095 '99999S' 0109E | |
| +---------------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
A "formatter" that defines fewer positions than are required by numeric values causes
the data to be returned as follows:
* Asterisks appear when the integer portion cannot be accommodated.
* When only the integer portion can be accommodated, any digits to the right of the least
significant digit are either truncated (for an integer value) or rounded (for a floating,
number, or decimal value).
Rounding is based on “Round to the Nearest” mode, as illustrated by the following process.
* Let B represent the actual result.
* Let A and C represent the nearest bracketing values that can be represented, such that
A < B < C.
* The determination as to whether A or C is the represented result is made as follows:
* When possible, the result is the value nearest to B.
* If A and C are equidistant (for example, the fractional part is exactly .5),
the result is the even number.
* Date type FORMAT to SPECIFIC FORMAT
The date and time formatting characters in a "formatter" determine the output of
DATE, TIME, and TIMESTAMP teradatasqlalchemy types information.
Following tables provide information about different formatters on
date type columns:
+--------------------------------------------------------------------------------------------------+
| formatter description |
+--------------------------------------------------------------------------------------------------+
| MMMM Represent the month as a full month name, such as |
| November. |
| M4 Valid names are specified by LongMonths in the |
| current SDF. |
| M4 is equivalent to MMMM, and is preferable to allow for a|
| shorter, unambiguous format string. |
| |
| You cannot specify M4 in a format that also has M3 or MM. |
| Examples: |
| +-------------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------------+ |
| | 19/01/16 'M4' JANUARY | |
| +-------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| |
| MMM Represent the month as an abbreviated month name, such as |
| 'Apr' for April. |
| M3 Valid names are specified by ShortMonths in the current |
| SDF. |
| |
| M3 is equivalent to MMM, and is preferable to allow for a |
| shorter, unambiguous format string. |
| |
| You cannot specify MMM in a format that also has MM. |
| Examples: |
| +-------------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------------+ |
| | 19/01/16 'M4' Jan | |
| +-------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| |
| MM Represent the month as two numeric digits. |
| Examples: |
| +-------------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------------+ |
| | 19/01/16 'MM' 01 | |
| +-------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| |
| DDD Represent the date as the sequential day in the year, |
| using three numeric digits, such as '032' as February 1. |
| |
| D3 D3 is equivalent to DDD, and allows for a shorter format |
| string. |
| You cannot specify DDD or D3 in a format that also has DD.|
| Examples: |
| +-------------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------------+ |
| | 19/02/13 'D3' 044 | |
| +-------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| |
| DD Represent the day of the month as two numeric digits. |
| Examples: |
| +-------------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------------+ |
| | 19/01/16 'DD' 16 | |
| +-------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| |
| YYYY Represent the year as four numeric digits. |
| |
| Y4 Y4 is equivalent to YYYY, and allows for a shorter format |
| string. |
| |
| You cannot specify YYYY or Y4 in a format that also has |
| YY. |
| Examples: |
| +-------------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------------+ |
| | 19/01/16 'Y4' 2019 | |
| +-------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| |
| YY Represent the year as two numeric digits. |
| Examples: |
| +-------------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------------+ |
| | 19/01/16 'YY' 19 | |
| +-------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| |
| EEEE Represent the day of the week using the full name, |
| such as Thursday. |
| |
| E4 Valid names are specified by LongDays in the current SDF. |
| |
| E4 is equivalent to EEEE, and allows for a shorter format |
| string. |
| Examples: |
| +-------------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------------+ |
| | 19/01/16 'E4' Wednesday | |
| +-------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| |
| EEE Represent the day of the week as an abbreviated name, such|
| as 'Mon' for Monday. |
| E3 Valid abbreviations are specified by ShortDays in the |
| current SDF. |
| E3 is equivalent to EEE, and allows for a shorter format |
| string. |
| Examples: |
| +-------------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------------+ |
| | 19/01/16 'E3' Wed | |
| +-------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| |
| / Slash separator. |
| Copied to output string where it appears in the FORMAT |
| phrase. |
| This is the default separator for Teradata dates. |
| Examples: |
| +-------------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------------+ |
| | 19/01/16 'M4/E3' January/Wed | |
| +-------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| |
| B Blank representation separator. |
| |
| b Use this instead of a space to represent a blank. |
| Examples: |
| +-------------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------------+ |
| | 19/01/16 'M4BE3' JANUARY Wed | |
| +-------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| |
| , Comma separator. |
| Copied to output string where it appears in the FORMAT |
| phrase. |
| Examples: |
| +-------------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------------+ |
| | 19/01/16 'M4,E3' January,Wed | |
| +-------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| |
| : Colon separator. |
| Copied to output string where it appears in the FORMAT |
| phrase. |
| Examples: |
| +-------------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------------+ |
| | 19/01/16 'M4:E3' January:Wed | |
| +-------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| |
| . Period separator. |
| Copied to output string where it appears in the FORMAT |
| phrase. |
| Examples: |
| +-------------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------------+ |
| | 19/01/16 'M4.E3' January.Wed | |
| +-------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| |
| - Dash separator. |
| Copied to output string where it appears in the FORMAT |
| phrase. |
| This is the default separator for ANSI dates. |
| Examples: |
| +-------------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------------+ |
| | 19/01/16 'M4-E3' January-Wed | |
| +-------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| |
| 9 Decimal digit. |
| This formatting character can only be used with separators|
| less than 0x009F. |
| |
| The 9(n) notation can be used for more than one occurrence|
| of this character, where n is an integer constant and |
| means that the '9' repeats n number of times. |
| |
| This formatting character is for DATE and PERIOD(DATE) |
| types only and cannot appear as a date formatting |
| character for PERIOD(TIMESTAMP) and TIMESTAMP types. |
| Examples: |
| +-------------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------------+ |
| | 19/01/16 '999999' 190116 | |
| +-------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| |
| Z Zero-suppressed decimal digit. |
| This formatting character can only be used with separators|
| less than 0x009F. |
| |
| The Z(n) notation can be used for more than one occurrence|
| of this character, where n is an integer constant and |
| means that the 'Z' repeats n number of times. |
| |
| This formatting character is for DATE and PERIOD(DATE) |
| types only and cannot appear as a date formatting |
| character for PERIOD(TIMESTAMP) and TIMESTAMP types. |
| Examples: |
| +-------------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------------+ |
| | 19/01/16 'ZZZZZZ' 190116 | |
| +-------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
Following tables provide information about different formatters on
time type columns:
+--------------------------------------------------------------------------------------------------+
| formatter description |
+--------------------------------------------------------------------------------------------------+
| |
| HH Represent the hour as two numeric digits. |
| Examples: |
| +-------------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------------+ |
| | 2020-07-01 08:00:00.000000 'HH' 08 | |
| +-------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| |
| MI Represent the minute as two numeric digits. |
| Examples: |
| +-------------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------------+ |
| | 2020-07-01 13:20:53.64+03:00: 'HH:MI' 13:20 | |
| +-------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| |
| SS Represent the second as two numeric digits. |
| Examples: |
| +-------------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------------+ |
| | 2020-07-01 13:20:53.64+03:00: 'HH.MI.SS' 13.20.53 | |
| +-------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| |
| S(n) Number of fractional seconds. |
| |
| S(F) Replace n with a number between 0 and 6, or use F for the |
| number of characters needed to display the fractional |
| seconds precision. |
| |
| The value of F is resolved during the formatting of the |
| TIME or TIMESTAMP data. The value is obtained from the |
| fractional seconds precision in the declaration of the |
| data type. For example, F is two for the TIME(2) type. |
| |
| A value of zero for F displays no radix symbol and no |
| fractional precision for the data. |
| The S(F) formatting characters must follow a D formatting |
| character or a . separator character. |
| |
| A value of n that is less than the PERIOD(TIME), |
| PERIOD(TIMESTAMP), TIME or TIMESTAMP fractional second |
| precision produces an error. |
| Examples: |
| +-------------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------------+ |
| |2020-07-01 13:20:53.64+03:00:'HH:MI:SSDS(F)'13:20:53.64| |
| +-------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| |
| D Radix symbol. |
| The value of RadixSeparator in the current SDF is copied |
| to the output string whenever a D appears in the FORMAT |
| phrase. |
| |
| Separator characters, such as . or :, can also appear in |
| the "formatter", but only if they do not match the value |
| of RadixSeparator. |
| Examples: |
| +-------------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------------+ |
| |2020-07-01 13:20:53.64+03:00:'HH:MI:SSDS(F)'13:20:53.64| |
| +-------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| |
| T Represent time in 12-hour format instead of 24-hour |
| format. The appropriate time of day, as specified by AMPM |
| in the current SDF is copied to the output string |
| where a T appears in the "formatter". |
| |
| Examples: |
| +-------------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------------+ |
| |2020-07-01 13:20:53.64+03:00:'HH:MI:SSBT'01:20:53 Nachm| |
| | (Nachm is German for PM.)| |
| +-------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| |
| Z Time zone. |
| The Z controls the placement of the time zone in the |
| output of PERIOD(TIME), PERIOD(TIMESTAMP), TIME and |
| TIMESTAMP data, and can only appear at the beginning or |
| end of the time formatting characters. |
| |
| For example, the following statement uses a "formatter" |
| that includes a Z before the time formatting characters: |
| |
| SELECT CURRENT_TIMESTAMP |
| (FORMAT 'YYYY-MM-DDBZBHH:MI:SS.S(6)'); |
| If the PERIOD(TIME), PERIOD(TIMESTAMP), TIME or |
| TIMESTAMP teradatasqlalchemy types contains time zone |
| data, the time zone is copied to the output string. |
| The time zone format is +HH:MI or -HH:MI, depending |
| on the time zone hour displacement. |
| Examples: |
| +------------------------------------------------------------+|
| | data formatter result ||
| +------------------------------------------------------------+|
| |2020-07-01 13:20:53.64+03:00: 'HH:MI:SSDS(F)Z' 13:20:53.64||
| | +03:00||
| +------------------------------------------------------------+|
+--------------------------------------------------------------------------------------------------+
| |
| : Colon separator. |
| Copied to output string where it appears in the FORMAT |
| phrase. This is the default separator for ANSI time. |
| |
| This character cannot appear in the "formatter" if the |
| value of RadixSeparator in the current SDF is a colon. |
| Examples: |
| +-------------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------------+ |
| | 2020-07-01 13:20:53.64+03:00: 'HH:MI' 13:20 | |
| +-------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| |
| . Period separator. |
| This can also be used to indicate the fractional seconds. |
| |
| Copied to output string where it appears in the FORMAT |
| phrase. |
| |
| This character cannot appear in the "formatter" if the |
| value of RadixSeparator in the current SDF is a period. |
| Examples: |
| +-------------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------------+ |
| | 2020-07-01 13:20:53.64+03:00: 'HH.MI.SS' 13.20.53 | |
| +-------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| |
| - Dash separator. |
| Copied to output string where it appears in the FORMAT |
| phrase. |
| Examples: |
| +-------------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------------+ |
| | 2020-07-01 13:20:53.64+03:00: 'HH-MI' 13-20 | |
| +-------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| |
| h Hour separator. |
| A lowercase h character is copied to the output string. |
| |
| The h formatting character must follow the HH formatting |
| characters. |
| |
| This character cannot appear in the "formatter" if the |
| value of RadixSeparator in the current SDF is a lowercase |
| h character. |
| Examples: |
| +-------------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------------+ |
| | 2020-07-01 13:20:53.64+03:00: 'HHhMImSSs' 13h20m53s| |
| +-------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| |
| m Minute separator. |
| A lowercase m character is copied to the output string. |
| |
| The m formatting character must follow the MI formatting |
| characters. |
| |
| This character cannot appear in the "formatter" if the |
| value of RadixSeparator in the current SDF is a lowercase |
| m character. |
| Examples: |
| +-------------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------------+ |
| | 2020-07-01 13:20:53.64+03:00: 'HHhMImSSs' 13h20m53s| |
| +-------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| |
| s Second separator. |
| A lowercase s character is copied to the output string. |
| |
| The s formatting character must follow SS or SSDS(F) |
| formatting characters. |
| |
| This character cannot appear in the "formatter" if the |
| value of RadixSeparator in the current SDF is a lowercase |
| s character. |
| Examples: |
| +-------------------------------------------------------+ |
| | data formatter result | |
| +-------------------------------------------------------+ |
| | 2020-07-01 13:20:53.64+03:00: 'HHhMImSSs' 13h20m53s| |
| +-------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
| |
| B Blank representation separator. |
| |
| b Use this instead of a space to represent a blank. |
| |
| This character cannot appear in the "formatter" if the |
| value of RadixSeparator in the current SDF is a blank. |
| Examples: |
| +--------------------------------------------------------------+ |
| | data formatter result | |
| +--------------------------------------------------------------+ |
| | 2020-07-01 13:20:53.64+03:00: 'MM/DD/YYBHH:MIBT' 07/01/20 | |
| | 01:20 PM | |
| +--------------------------------------------------------------+ |
+--------------------------------------------------------------------------------------------------+
RETURNS:
ColumnExpression.
EXAMPLES:
# Load the data to run the example.
>>> load_example_data("dataframe", "admissions_train")
>>> load_example_data("uaf", "stock_data")
# Create a DataFrame on 'admissions_train' table.
>>> admissions_train=DataFrame("admissions_train")
>>> admissions_train
masters gpa stats programming admitted
id
34 yes 3.85 Advanced Beginner 0
32 yes 3.46 Advanced Beginner 0
11 no 3.13 Advanced Advanced 1
30 yes 3.79 Advanced Novice 0
28 no 3.93 Advanced Advanced 1
16 no 3.70 Advanced Advanced 1
9 no 3.82 Advanced Advanced 1
13 no 4.00 Advanced Novice 1
15 yes 4.00 Advanced Advanced 1
17 no 3.83 Advanced Advanced 1
>>>
# Example 1: Round the 'age' column upto 1 decimal values.
>>> format_df = admissions_train.assign(format_column=admissions_train.gpa.format("zz.z"))
>>> format_df
masters gpa stats programming admitted format_column
id
38 yes 2.65 Advanced Beginner 1 2.6
7 yes 2.33 Novice Novice 1 2.3
26 yes 3.57 Advanced Advanced 1 3.6
5 no 3.44 Novice Novice 0 3.4
3 no 3.70 Novice Beginner 1 3.7
22 yes 3.46 Novice Beginner 0 3.5
24 no 1.87 Advanced Novice 1 1.9
36 no 3.00 Advanced Novice 0 3.0
19 yes 1.98 Advanced Advanced 0 2.0
40 yes 3.95 Novice Beginner 0 4.0
>>>
# Create a DataFrame on 'stock_data' table.
>>> stock_data=DataFrame("stock_data")
>>> stock_data
seq_no timevalue magnitude
data_set_id
556 3 19/01/16 61.080
556 5 19/01/30 63.810
556 6 19/02/06 63.354
556 7 19/02/13 63.871
556 9 19/02/27 61.490
556 10 19/03/06 61.524
556 8 19/02/20 61.886
556 4 19/01/23 63.900
556 2 19/01/09 61.617
556 1 19/01/02 60.900
>>>
# Example 2: change the format of 'timevalue' column.
>>> format_df = stock_data.assign(format_column=stock_data.timevalue.format('MMMBDD,BYYYY'))
>>> format_df
seq_no timevalue magnitude format_column
data_set_id
556 3 19/01/16 61.080 Jan 16, 2019
556 5 19/01/30 63.810 Jan 30, 2019
556 6 19/02/06 63.354 Feb 06, 2019
556 7 19/02/13 63.871 Feb 13, 2019
556 9 19/02/27 61.490 Feb 27, 2019
556 10 19/03/06 61.524 Mar 06, 2019
556 8 19/02/20 61.886 Feb 20, 2019
556 4 19/01/23 63.900 Jan 23, 2019
556 2 19/01/09 61.617 Jan 09, 2019
556 1 19/01/02 60.900 Jan 02, 2019
>>>