Skip to main content

​
Description

Parses an input string to a datetime value using the supplied format. Returns null when the input is null, empty, blank, or does not match the format, instead of failing the job. Use ToDateOrNull when a column contains a mix of parseable and unparseable values and you want the bad rows to become null rather than stop the run. For a strict variant that throws on bad input, use ToDate. For multi-format fallback, use ToDateCustomFormats.

​
Syntax

ToDateOrNull(dtStr, format)

​
Arguments

  • dtStr - datetime value in a string expression.
  • format - format of the input string. Uses the same Joda-Time pattern letters as ToDate.

​
Examples

  • ToDateOrNull('2013-10-17 00:00:00', 'yyyy-MM-dd HH:mm:ss') returns 2013-10-17T00:00:00.000Z
  • ToDateOrNull('17/10/2013', 'dd/MM/yyyy') returns 2013-10-17T00:00:00.000Z
  • ToDateOrNull('31/02/2020', 'dd/MM/yyyy') returns null (impossible calendar date)
  • ToDateOrNull('abc', 'dd/MM/yyyy') returns null
  • ToDateOrNull('', 'dd/MM/yyyy') returns null

​
Return value datatype

datetime

​
Impact of null value

If dtStr is null, empty, or whitespace only, returns null. If format is null or missing, an exception is thrown.
Last modified on July 6, 2026