Convert dates and times

Convert strings into dates, times, or intervals, or format time and dates into strings.

You can perform a bulk change of a date field (date, time, or date+time) into a string field or a string field into a date field.

To perform date and time conversion:

  1. In the New operation panel, click Date/Time.
    The Trillion-Row Spreadsheet displays the Date and Time Conversion panel.

  2. From the Column drop-down list, select the column you wish to convert.
    The lower part of the Data and Time Conversion panel changes depending on which column you selected. If you selected an integer column, the panel assumes you have selected a date format and will present string conversion options. If you selected a text column, the panel assumes you want to convert the string to a date format and will present date conversion options.

  3. If converting a date/time to string format: Select the format for your date string from the Format drop-down list.
    Note: The drop-down list also contains an example of each option.
  4. If converting a string to a date/time format:
    1. Select the datatype from the Datatype drop-down list.
    2. Select the order hint from the drop-down list, if applicable.
      Note: The order hint helps the operation parse the date correctly if there is any ambiguity to the date string. For example, if the order hint is Month/Day/Year and the string is "1/2/3", the operation would parse the date as January 2, 2003.
    Note: For detailed information about date/time formats in the 1010data Insights Platform, see Dates and time.
  5. Enter the name and label for the new column containing the converted data.
    Name

    This is the name the 1010data Insights Platform uses to interact with the column. It is also used when writing more advanced value expressions and queries.

    The column name must be unique. It may only contain alphanumeric characters or underscores and must begin with an alphabetic character (e.g., percent_total_sales). It may not contain any spaces or other special characters.

    This is a required field.
    Note: When entered, the Trillion-Row Spreadsheet (TRS) automatically removes special characters, replaces spaces with underscores, and displays a visual indication that a change was made.

    When TRS makes these changes, and if nothing has been entered in the Label field, the original text is automatically copied into the Label field.
    Label

    This is the column heading that displays by default at the top of a column in the user interface.

    The column label may contain any combination of uppercase and lowercase letters, numbers, spaces, and special characters. If you want to have a multi-line column label, use the backtick character (`) to separate the lines (e.g., "Percentage of`Total Sales (%)").
    Note: On most standard United States keyboards, the backtick (`) character is immediately to the left of 1 key.

    While not required, this field is recommended.
  6. Click the Submit operation () icon.
    The Trillion-Row Spreadsheet displays the results of the date conversion operation. In this example, the Date column is converted from an integer column to a string column called String Date with the format "YYYY-MM-DD".