Designer Cloud Powered by Trifacta® Enterprise Edition supports a variety of Datetime formats, each of which has additional variations to it.
Date Range
Supported Date Ranges:
- Earliest: January 1, 1400
- Latest: December 31, 2599
NOTE: The supported date ranges can be modified if needed. For more information, see Configure Application Limits.
Formatting Tokens
You can use the following tokens to change the format of a column of dates:
| Letter | Date or Time Component | Presentation | Examples |
|---|---|---|---|
| M | Month in year | Number | 1 |
| MM | Month in year | Number | 01 |
| MMMM | Month in year | Month | January |
| MMM | Month in year | Month | Jan |
| yy | Year | Number | 16 |
| yyyy | Year | Number | 2016 |
| D | Day in year | Number | 352 |
| d | Day in month | Number | 9 |
| dd | Day in a month | Number | 09 |
| EEE | Day in week (three-letter abbreviation) | Text | Wed |
| EEEE | Day in week | Text | Wednesday |
| h | Hour in day (1-12) NOTE: Requires an AM/PM indicator ( | Number | 2 |
| hh | Hour in am/pm (01-12) NOTE: Requires an AM/PM indicator ( | Number | 02 |
| H | Hour in day (1-12) | Number | 2 |
| HH | Hour in day (0-23) | Number | 20 |
| m | Minute in an hour | Number | 9 |
mm | Minute in an hour | Number | 09 |
| s | Second in a minute | Number | 3 |
| ss | Second in a minute | Number | 03 |
| SSS | Millisecond | Number | 218 |
| X | Time zone | ISO 8601 time zone | -08:00 |
| a | AM/PM indicator | String | AM |
NOTE: When publishing to relational targets, Datetime values are written as date/time values in newly created tables. If you are appending to a relational table column that is in timestamp format, Datetime values can be written as timestamps.
Tip: If your DateTime column contains data in multiple formats, you must change the format of the DateTime column to one format and then add a transform to convert that data to the other format. When all formats of your source date values are converted to a single format, the application should infer the appropriate date and time format.
Supported Separators:
- Date separators: blank space, comma, single hyphen, or forward slash
- Time separators: blank space, comma, single hyphen, colon, t or T
- Non-delimited Datetime values are supported. For example, yyyymmdd, yyyymmddThhmmssX.
ISO 8601 Time Zone Notes:
Support for timezone offset from UTC indicated by +hh:mm, +hhmm, or +hh. For example, the date '2013-11-18 11:55-04:00' is recognized as a DateTime value.
- Datetime part functions (for example, Hour) truncate time zones and return local time.
- If you have a column with multiple time zones, you can convert the column to Unixtime so you can perform Date/Time operations with a standardized time zone. If you want to work with local times, you can truncate the time zone or use other Datetime functions. See UNIXTIME Function.
This page has no comments.