Interpret Column as Date/DateTime
Converts a column from text to a date or datetime type by parsing it using the specified format.
Example
| id | purchase_date |
| 15 | 20.01.2022 |
| 16 | 22.01.202 |
Date Format:
dd.MM.yyyy
| id | purchase_date_date |
| 15 | 2022-01-20 |
| 16 | 2021-01-22 |
Configuration
The following option(s) are available when configuring this transform.
Parse Type
Select Date or Date and Time to parse text into the required type.
Column
Select the column to be interpreted.
Format
Specify the date format in the text. You can specify multiple formats, separated by commas, that will be tried in order. If your format contains a comma it should be wrapped in double quotes.
Where
Add a condition to specify when the transform should be applied.
Format Patterns
The following letters are supported to specify the date or datetime format:
| Letter | Date or Time | Presentation | Example |
|---|---|---|---|
| G | Era designator | Text | AD |
| y | Year | Year | 1996; 96 |
| Y | Week year | Year | 2009; 09 |
| M | Month in year | Month | July; Jul; 07 |
| w | Week in year | Number | 27 |
| W | Week in month | Number | 2 |
| D | Day in year | Number | 189 |
| d | Day in month | Number | 10 |
| F | Day of week in month | Number | 2 |
| E | Day name in week | Text | Tuesday; Tue |
| u | Day number of week (1 = Monday, …, 7 = Sunday) | Number | 1 |
| a | Am/pm marker | Text | PM |
| H | Hour in day (0–23) | Number | 0 |
| k | Hour in day (1–24) | Number | 24 |
| K | Hour in am/pm (0–11) | Number | 0 |
| h | Hour in am/pm (1–12) | Number | 12 |
| m | Minute in hour | Number | 30 |
| s | Second in minute | Number | 55 |
| S | Millisecond | Number | 978 |
| z | Time zone | General time zone | Pacific Standard Time; PST; GMT-08:00 |
| Z | Time zone | RFC 822 time zone | -0800 |
| X | Time zone | ISO 8601 time zone | -08; -0800; -08:00 |
Key Considerations
- The column types change to
dateordatetimewhen adding this transform. - If a selected column is an invalid date format, the column value is set to
INVALID_DATEand the column’s type is set tostring. Tn this case, the Where clause can be used to conditionally apply this transform for any special cases.