Teradata Package for Python Function Reference | 20.00 - to_date - 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
Published
March 2024
Language
English (United States)
Last Update
2024-04-10
dita:id
TeradataPython_FxRef_Enterprise_2000
Product Category
Teradata Vantage
teradataml.dataframe.sql.DataFrameColumn.to_date = to_date(self, formatter=None)
DESCRIPTION:
    Convert a string-like representation of a DATE or PERIOD type to Date type.
 
PARAMETERS:
    formatter:
        Optional Argument.
        Specifies a variable length string containing formatting characters
        that define the format of column.
        Type: str
        Note:
            * If "formatter" is omitted, the following default date format is used: 'YYYY-MM-DD'
        * formatter for date type:
            +--------------------------------------------------------------------------------------------------+
            |    FORMATTER                             DESCRIPTION                                             |
            +--------------------------------------------------------------------------------------------------+
            |    -                                                                                             |
            |    /                                                                                             |
            |    ,                                     Punctuation characters are ignored and text enclosed in |
            |    .                                     quotation marks is ignored.                             |
            |    ;                                                                                             |
            |    :                                                                                             |
            |    "text"                                                                                        |
            |                                         Example: Date with value '2003-12-10'                    |
            |                                              +-------------------------------------------------+ |
            |                                              | data             formatter          value       | |
            |                                              +-------------------------------------------------+ |
            |                                              | '2003-12-10'     YYYY-MM-DD         03/12/10    | |
            |                                              +-------------------------------------------------+ |
            +--------------------------------------------------------------------------------------------------+
            |    D                                     Day of week (1-7).                                      |
            |                                          Example: day of week with value '2'                     |
            |                                              +-------------------------------------------------+ |
            |                                              | data            formatter           value       | |
            |                                              +-------------------------------------------------+ |
            |                                              | 2               D                   24/01/01    | |
            |                                              +-------------------------------------------------+ |
            +--------------------------------------------------------------------------------------------------+
            |    DAY                                   Name of day.                                            |
            |                                          Example: Date with value '2024-TUESDAY-01-30'           |
            |                                              +-------------------------------------------------+ |
            |                                              | data                formatter         value     | |
            |                                              +-------------------------------------------------+ |
            |                                              | 2024-TUESDAY-01-30   YYYY-DAY-MM-DD   24/01/30  | |
            |                                              +-------------------------------------------------+ |
            +--------------------------------------------------------------------------------------------------+
            |    DD                                    Day of month (1-31).                                    |
            |                                          Example: Date with value '2003-10-25'                   |
            |                                              +-------------------------------------------------+ |
            |                                              | data            formatter           value       | |
            |                                              +-------------------------------------------------+ |
            |                                              | 2003-10-25       YYYY-MM-DD         03/10/25    | |
            |                                              +-------------------------------------------------+ |
            +--------------------------------------------------------------------------------------------------+
            |    DDD                                   Day of year (1-366).                                    |
            |                                          Example: Date with value '2024-366'                     |
            |                                              +-------------------------------------------------+ |
            |                                              | data            formatter           value       | |
            |                                              +-------------------------------------------------+ |
            |                                              | 2024-366       YYYY-DDD             24/12/31    | |
            |                                              +-------------------------------------------------+ |
            +--------------------------------------------------------------------------------------------------+
            |    DY                                    abbreviated name of day.                                |
            |                                          Example: Date with value '2024-Mon-01-29'               |
            |                                              +-------------------------------------------------+ |
            |                                              | data            formatter           value       | |
            |                                              +-------------------------------------------------+ |
            |                                              | 2024-Mon-01-29   YYYY-DY-MM-DD   24/01/29       | |
            |                                              +-------------------------------------------------+ |
            +--------------------------------------------------------------------------------------------------+
            |    HH                                                                                            |
            |    HH12                                 Hour of day (1-12).                                      |
            |                                          Example: Date with value '2016-01-06 09:08:01'          |
            |                                              +-------------------------------------------------+ |
            |                                              | data                formatter            value  | |
            |                                              +-------------------------------------------------+ |
            |                                              | 2016-01-06 09:08:01 YYYY-MM-DD HH:MI:SS  6/01/06| |
            |                                              +-------------------------------------------------+ |
            +--------------------------------------------------------------------------------------------------+
            |    HH24                                 Hour of the day (0-23).                                  |
            |                                          Example:  Date with value '2016-01-06 23:08:01'         |
            |                                           +----------------------------------------------------+ |
            |                                           | data                formatter              value   | |
            |                                           +----------------------------------------------------+ |
            |                                           | 2016-01-06 23:08:01 YYYY-MM-DD HH24:MI:SS  6/01/06 | |
            |                                           +----------------------------------------------------+ |
            +--------------------------------------------------------------------------------------------------+
            |    J                                    Julian day, the number of days since January 1, 4713 BC. |
            |                                         Number specified with J must be integers.                |
            |                                         Teradata uses the Gregorian calendar in calculations to  |
            |                                         and from Julian Days.                                    |
            |                                          Example: Number of julian days with value '2457394'     |
            |                                              +-------------------------------------------------+ |
            |                                              | data            formatter           value       | |
            |                                              +-------------------------------------------------+ |
            |                                              | 2457394         J                   16/01/06    | |
            |                                              +-------------------------------------------------+ |
            +--------------------------------------------------------------------------------------------------+
            |    MI                                   Minute (0-59).                                           |
            |                                          Example: Date with value '2016-01-06 23:08:01'          |
            |                                           +----------------------------------------------------+ |
            |                                           | data                formatter              value  | |
            |                                           +----------------------------------------------------+ |
            |                                           | 2016-01-06 23:08:01 YYYY-MM-DD HH24:MI:SS  6/01/06 | |
            |                                           +----------------------------------------------------+ |
            +--------------------------------------------------------------------------------------------------+
            |    MM                                   Month (01-12).                                           |
            |                                          Example: Date with value '2016-01-06 23:08:01'          |
            |                                           +----------------------------------------------------+ |
            |                                           | data                formatter              value  | |
            |                                           +----------------------------------------------------+ |
            |                                           | 2016-01-06 23:08:01 YYYY-MM-DD HH24:MI:SS  6/01/06 | |
            |                                           +----------------------------------------------------+ |
            +--------------------------------------------------------------------------------------------------+
            |    MON                                  Abbreviated name of month.                               |
            |                                          Example: Date with value '2016-JAN-06'                  |
            |                                           +----------------------------------------------------+ |
            |                                           | data                formatter              value   | |
            |                                           +----------------------------------------------------+ |
            |                                           | 2016-JAN-06         YYYY-MON-DD           16/01/06 | |
            |                                           +----------------------------------------------------+ |
            +--------------------------------------------------------------------------------------------------+
            |    MONTH                                Name of month.                                           |
            |                                          Example: Date with value '2016-JANUARY-06'              |
            |                                              +-------------------------------------------------+ |
            |                                              | data            formatter              value    | |
            |                                              +-------------------------------------------------+ |
            |                                              | 2016-JANUARY-06  YYYY-MONTH-DD         16/01/06 | |
            |                                              +-------------------------------------------------+ |
            +--------------------------------------------------------------------------------------------------+
            |    PM                                                                                            |
            |    P.M.                                 Meridian indicator.                                      |
            |                                          Example:  Date with value '2016-01-06 23:08:01 PM'      |
            |                                      +---------------------------------------------------------+ |
            |                                      | data                   formatter                value   | |
            |                                      +---------------------------------------------------------+ |
            |                                      | 2016-01-06 23:08:01 PM YYYY-MM-DD HH24:MI:SS PM 16/01/06| |
            |                                      +---------------------------------------------------------+ |
            +--------------------------------------------------------------------------------------------------+
            |    RM                                   Roman numeral month (I - XII).                           |
            |                                          Example: Date with value '2024-XII'                     |
            |                                              +-------------------------------------------------+ |
            |                                              | data            formatter           value       | |
            |                                              +-------------------------------------------------+ |
            |                                              | 2024-XII       YYYY-RM             24/12/01     | |
            |                                              +-------------------------------------------------+ |
            +--------------------------------------------------------------------------------------------------+
            |    RR                                   Stores 20th century dates in the 21st century using only |
            |                                         2 digits. If the current year and the specified year are |
            |                                         both in the range of 0-49, the date is in the current    |
            |                                         century.                                                 |
            |                                         Example: Date with value '2024-365, 21'                  |
            |                                              +-------------------------------------------------+ |
            |                                              | data            formatter           value       | |
            |                                              +-------------------------------------------------+ |
            |                                              | 2024-365, 21      YYYY-DDD, RR      21/12/31    | |
            |                                              +-------------------------------------------------+ |
            +--------------------------------------------------------------------------------------------------+
            |    RRRR                                 Round year. Accepts either 4-digit or 2-digit input.     |
            |                                         2-digit input provides the same return as RR.            |
            |                                          Example: Date with value '2024-365, 21'                  |
            |                                              +-------------------------------------------------+ |
            |                                              | data            formatter           value       | |
            |                                              +-------------------------------------------------+ |
            |                                              | 2024-365, 21       YYYY-DDD, RRRR   24/12/31    | |
            |                                              +-------------------------------------------------+ |
            +--------------------------------------------------------------------------------------------------+
            |    SS                                   Second (0-59).                                           |
            |                                          Example: Date with value '2016-01-06 23:08:01'          |
            |                                           +----------------------------------------------------+ |
            |                                           | data                formatter              value   | |
            |                                           +----------------------------------------------------+ |
            |                                           | 2016-01-06 23:08:01 YYYY-MM-DD HH24:MI:SS  6/01/06 | |
            |                                           +----------------------------------------------------+ |
            +--------------------------------------------------------------------------------------------------+
            |    SSSSS                                Seconds past midnight (0-86399).                         |
            +--------------------------------------------------------------------------------------------------+
            |    TZH                                  Time zone hour.                                          |
            +--------------------------------------------------------------------------------------------------+
            |    TZM                                  Time zone minute.                                        |
            +--------------------------------------------------------------------------------------------------+
            |    X                                    Local radix character.                                   |
            |                                         Example: Date with value '2024.366'                      |
            |                                              +-------------------------------------------------+ |
            |                                              | data            formatter           value       | |
            |                                              +-------------------------------------------------+ |
            |                                              | 2024.366       YYYYXDDD             24/12/31    | |
            |                                              +-------------------------------------------------+ |
            +--------------------------------------------------------------------------------------------------+
            |    Y,YYY                                Year with comma in this position.                        |
            |                                          Example: Date with value '2,024-366'                    |
            |                                              +-------------------------------------------------+ |
            |                                              | data            formatter           value       | |
            |                                              +-------------------------------------------------+ |
            |                                              | 2,024-366       Y,YYY-DDD           24/12/31    | |
            |                                              +-------------------------------------------------+ |
            +--------------------------------------------------------------------------------------------------+
            |    YYYY                                                                                          |
            |    SYYYY                                4-digit year. S prefixes BC dates with a minus sign.     |
            |                                          Example: Date with value '2024-366'                     |
            |                                              +-------------------------------------------------+ |
            |                                              | data            formatter           value       | |
            |                                              +-------------------------------------------------+ |
            |                                              | 2024-366       YYYY-DDD             24/12/31    | |
            |                                              +-------------------------------------------------+ |
            +--------------------------------------------------------------------------------------------------+
            |    YYY                                  Last 3, 2, or 1 digit of year.                           |
            |    YY                                   If the current year and the specified year are both in   |
            |    Y                                    the range of 0-49, the date is in the current century.   |
            |                                          Example: Date with value '24-366'                       |
            |                                              +-------------------------------------------------+ |
            |                                              | data            formatter       value           | |
            |                                              +-------------------------------------------------+ |
            |                                              | 24-366       YY-DDD             24/12/31        | |
            |                                              +-------------------------------------------------+ |
            +--------------------------------------------------------------------------------------------------+
 
RAISES:
    TypeError, ValueError, TeradataMlException
 
Returns:
    ColumnExpression
 
EXAMPLES:
    # Load the data to run the example.
    >>> load_example_data("uaf", "stock_data")
 
    # Create a DataFrame on 'stock_data' table.
    >>> df = DataFrame("stock_data")
    >>> df
                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
 
    # create new_column "timevalue_char" using to_char().
    >>> new_df = df.assign(timevalue_char=df.timevalue.to_char('DD-MON-YYYY'))
    >>> new_df
                    seq_no timevalue  magnitude timevalue_char
    data_set_id
    556               3  19/01/16     61.080    16-JAN-2019
    556               5  19/01/30     63.810    30-JAN-2019
    556               6  19/02/06     63.354    06-FEB-2019
    556               7  19/02/13     63.871    13-FEB-2019
    556               9  19/02/27     61.490    27-FEB-2019
    556              10  19/03/06     61.524    06-MAR-2019
    556               8  19/02/20     61.886    20-FEB-2019
    556               4  19/01/23     63.900    23-JAN-2019
    556               2  19/01/09     61.617    09-JAN-2019
    556               1  19/01/02     60.900    02-JAN-2019
 
    # Example 1: convert "timevalue_char" column to DATE type.
    >>> res = new_df.assign(timevalue_char=new_df.timevalue_char.to_date('DD-MON-YYYY'))
    >>> res
                seq_no timevalue  magnitude timevalue_char
    data_set_id
    556               3  19/01/16     61.080       19/01/16
    556               5  19/01/30     63.810       19/01/30
    556               6  19/02/06     63.354       19/02/06
    556               7  19/02/13     63.871       19/02/13
    556               9  19/02/27     61.490       19/02/27
    556              10  19/03/06     61.524       19/03/06
    556               8  19/02/20     61.886       19/02/20
    556               4  19/01/23     63.900       19/01/23
    556               2  19/01/09     61.617       19/01/09
    556               1  19/01/02     60.900       19/01/02
    >>> res.tdtypes
    column            type
    data_set_id       INTEGER()
    seq_no            INTEGER()
    timevalue            DATE()
    magnitude           FLOAT()
    timevalue_char       DATE()