Convert a string to date

Supported in: Batch, Faster, Streaming

Returns the date given a formatted string in accordance to the Java DateTimeFormatter. The default formats are yyyy-MM-dd and yyyy-MM-dd'T'HH:mm:ss.SSSXXX. The formats are run in order, the first matching format will be returned.

Expression categories: Cast, Datetime

Declared arguments

• String: String column to parse to date.
  Expression<String>
• optional Formats: Input a date format (e.g. yyyy-MM-dd or MM/dd/yyyy).
  List<Literal<String>>

Output type: Date

Examples

Example 1: Base case

Description: Date formats are optional

Argument values:

• String: 2020-04-28
• Formats: null

Output: 2020-04-28

---

Example 2: Base case

Description: Date formats are optional

Argument values:

• String: 2020-04-28T01:00:00.000Z
• Formats: null

Output: 2020-04-28

---

Example 3: Base case

Description: Dates parsed with a single format.

Argument values:

• String: 28-2020-04
• Formats: [dd-yyyy-MM]

Output: 2020-04-28

---

Example 4: Base case

Description: Multiple formats used to parse dates

Argument values:

• String: 28-2020-04
• Formats: [yyyy-MM-dd, dd-yyyy-MM]

Output: 2020-04-28

---

Example 5: Base case

Description: If dates are missing leading zeros, they are still parsed correctly using this "yyyy-M-d" format.

Argument values:

• String: date
• Formats: [yyyy-M-d]

---

Example 6: Base case

Description: Strings can contain a timestamp

Argument values:

• String: 28-2020-04 10:12:00 +01
• Formats: [dd-yyyy-MM HH:mm X]

Output: 2020-04-28

---

Example 7: Base case

Description: Timezone can change the date.

Argument values:

• String: 28-2020-04 10:12:00 +11
• Formats: [dd-yyyy-MM HH:mm X]

Output: 2020-04-27

---

Example 8: Null case

Description: Null columns remain null

Argument values:

• String: null
• Formats: [dd-yyyy-MM]

Output: null

---

Example 9: Edge case

Description: Invalid data is parsed to null

Argument values:

• String: 28-20201-14
• Formats: [dd-yyyy-MM]

Output: null

---

Example 10: Edge case

Description: Incorrect formats parse everything to null

Argument values:

• String: 04-2020-28
• Formats: [dd-yyyy-MM]

Output: null

---

Example 11: Edge case

Description: A day-of-year format defaults the year to 1970.

Argument values:

• String: 300
• Formats: [D]

Output: 1970-10-27

---

Example 12: Edge case

Description: A three-digit day-of-year format defaults the year to 1970.

Argument values:

• String: 365
• Formats: [DDD]

Output: 1970-12-31

---

Example 13: Edge case

Description: A format without a year defaults the year to 1970.

Argument values:

• String: 12-25
• Formats: [MM-dd]

Output: 1970-12-25

---

Example 14: Edge case

Description: A month-only format defaults the year to 1970 and the day to the 1st.

Argument values:

• String: 03
• Formats: [MM]

Output: 1970-03-01

---

Example 15: Edge case

Description: A single-digit year format defaults the month to January and the day to the 1st.

Argument values:

• String: 1
• Formats: [y]

Output: 0001-01-01

---

Example 16: Edge case

Description: A time-only format defaults the date to the Unix epoch (1970-01-01).

Argument values:

• String: 1
• Formats: [m]

Output: 1970-01-01

---

Example 17: Edge case

Description: A two-digit year format defaults the month to January and the day to the 1st.

Argument values:

• String: 99
• Formats: [yy]

Output: 2099-01-01

---

Example 18: Edge case

Description: A year-only format defaults the month to January and the day to the 1st.

Argument values:

• String: 2020
• Formats: [yyyy]

Output: 2020-01-01

---