To handle columns that include data with different 'date and time' formats in Dattos, you can follow these steps:

Read as alphanumeric: Initially, the column can be read as alphanumeric to preserve both the date and time. This prevents information loss during the initial read.
Add the Transform component: Connect the data source to a Transform component, where you can create new columns with custom formulas.
Use the LEFT() function: In the Transform component configuration, create a new custom column and use the LEFT() function to extract the data part that represents the date. This will allow you to isolate the date information from the time part.
- Syntax: LEFT({column}; num.characters)
  - text: Can be a text field from the layout or any other text-type value.
  - num.characters: Number of characters.
  - Example: LEFT({Date e Time}; 11)
    - Considering the value "16-Nov-2023 13:38:00" in the "Date and Time" column, the formula will return the value "16-Nov-2023."
- Convert with the DATE() function: Next, create a new column in the same component and use the DATE() function to convert the extracted data into the desired date format. This will facilitate specific date manipulation and analysis.
  - Syntax: DATE({column}; mask)
    - date_text: Can be a text field from the layout or any other text-type value
    - mask: It's the formatting pattern that the date is written in. The following patterns should be used for definition:
      - dd - Day, with 2 digits.
      - mm - Month, with 2 digits.
      - mmm - Abbreviated month name, with 3 letters. The month can be in Portuguese, English, or Spanish.
      - mmmm - Full month name. The month can be in Portuguese, English, or Spanish.
      - yy - Year, with 2 digits.
      - yyyy - Year, with 4 digits.
- Example: DATE({Data}; "mm-dd-yyyy")
  Considering the value "16-Nov-2023" in the "Date" column, the formula will return the value "11/20/2023" in the 'date' format.

This approach allows you to maintain flexibility and preserve the details of the date and time, even in situations where the system does not directly recognize the 'date and time' format.

Frequently Asked Questions (FAQ) - Data Preparation