Format Datetime directive

The FORMAT-DATETIME directive was introduced in CDAP 6.4.0.

The FORMAT-DATETIME directive formats CDAP datetime values to custom pattern strings.

To convert strings to datetime values, see PARSE-AS-DATETIME.

Syntax

format-datetime :datetime_column "format"

Usage Notes

Date and time formats are specified by date and time pattern strings. Within pattern strings, unquoted letters from 'A' to 'Z' and from 'a' to 'z' are interpreted as pattern letters representing the components of a date or time string. Text can be quoted using single quotes (') to avoid interpretation. Two single quotes '' represents a single quote. All other characters are not interpreted; they're simply copied into the output string during formatting or matched against the input string during parsing.

The FORMAT-DATETIME directive will format CDAP datetime values to custom pattern strings. Pattern is the format for the output string.

If the column is null, applying this directive is a no-op. The column to be formatted should be of type datetime.

Supported Patterns for Formatting and Parsing

CDAP supports the Java DateTimeFormatter patterns for formatting and parsing datetime values. For more information about the DateTimeFormatter patterns, see the DateTimeFormatter documentation.

Date and Time Pattern Examples

Prior to using these patterns, the directive PARSE-AS-DATETIME must be applied to correctly create a complete date-time string.

These examples show how date and time patterns are interpreted in the U.S. locale:

Input	Date and Time Pattern	Result

Input	Date and Time Pattern	Result
`2006-02-15T05:03:42Z[UTC]`	`yyyy-MM-dd'T'HH:mm:ssX'['z']'`	`2006-02-15T05:03:42`
`2001.07.04 AD at 12:08:56 PDT`	`yyyy.MM.dd G 'at' HH:mm:ss z`	`2001-07-04T12:08:56`
`2001.July.04 AD 12:08 PM`	`yyyy'.'MMMM'.'d G H:mm a`	`2001-07-04T12:08`
`Wed, 4 Jul 2001 12:08:56 -0700`	`EEE, d MMM yyyy HH:mm:ss Z`	`2001-07-04T12:08:56`
`010704120856-0700`	`yyMMddHHmmssZ`	`2001-07-04T12:08:56`
`2001-07-04T12:08:56.235-0700`	`yyyy-MM-dd'T'HH:mm:ss.SSSZ`	`2001-07-04T12:08:56.235`
`07/04/2001 12:09`	`MM/dd/yyyy HH:mm`	`2001-07-04T12:09`
`1/2/09 6:17`	`M/D/yy H:mm`	`2009-01-02T06:17`
`2011-12-03T10:15:30+01:00`	`yyyy-MM-dd'T'HH:mm:ss[xxx]`	`2011-12-03T10:15:30`
`2011-12-03T10:15:30+01:00[Europe/Paris]`	`yyyy-MM-dd'T'HH:mm:ss[xxx]'['VV']'`	`2011-12-03T10:15:30`

Troubleshooting

PARSE-AS-DATETIME is more restrictive than PARSE-AS-SIMPLEDATE. The date and time patterns for PARSE-AS-DATETIME must exactly match the input strings. For example, if you use the PARSE-AS-SIMPLEDATE directive with this input string 1/2/09 6:17, you can enter MM/dd/yy HH:mm as the format pattern and the Wrangler transforms the value to 2009-01-02T06:17Z[UTC]. However, if you use PARSE-AS-DATETIME on the same input string, 1/2/09 6:17 and enter MM/dd/yy HH:mm as the pattern, the following error occurs:

This is because the input string doesn’t exactly match the pattern. The correct pattern in this case is M/D/yy H:mm.

To resolve this error, follow these steps:

Click Transformation steps.
Delete the parse-as-datetime step.
Add the PARSE-AS-DATETIME directive again with a pattern that exactly matches the input string.