ADF Data Flow - toTimestamp() returning NULL on blob source filter

Question

I am trying to read files from Azure Blob Storage using a Data Flow, and only read files modified since a date stored in a database. I am trying to use the following setting "Filter by last modified" to limit the time range:

The source data is a SQL Server datetime2 and passed into the Data Flow as a parameter value StartTime. This field expects a timestamp.

• When the parameter is set as a timestamp, an error is generated referring to types not being honored.
• When the parameter is set as a date, and the filter value set to $StartTime the same error is shown, andtoTimestamp($StartTime) is rejected as an expression because toTimestamp() does not accept a date as a parameter.
• When the parameter is converted to a string at the pipeline level with @formatDateTime(<date>, 'yyyy-MM-dd HH:mm:ss.fff'), both toTimestamp($StartTime) and toTimestamp($StartTime, 'yyyy-MM-dd HH:mm:ss.fff') return NULL.

I can verify in that last case that the date is being picked up in the pipeline by using Data Flow Debug and a Derived Column transform:

In this case StartDateString is defined as $StartTime and StartDate as toTimestamp($StartTime, 'yyyy-MM-dd HH:mm:ss.fff').

Why is toTimestamp() rejecting my date string and returning NULL? Otherwise, how do I get the value from the database

Answer 1

Hi Chris J,

it turns out nothing is wrong with ADF’s Blob connector— toTimestamp() is simply failing to parse your string, so it returns NULL. Here’s why and how to fix it:

1. Java-style vs .NET-style format strings
   • Your pipeline’s @formatDateTime(..., 'yyyy-MM-dd HH:mm:ss.fff') is using the .NET “fff” specifier.
   • ADF Data Flow’s toTimestamp(str, format) uses Java’s SimpleDateFormat under the covers, so it expects “SSS” for milliseconds, not “fff”.
2. Make sure your string and pattern line up exactly
   • For example, if your string is 2026-04-24 13:18:34.698 then parse it with: toTimestamp(

          $StartTimeString,
          
          
          'yyyy-MM-dd HH:mm:ss.SSS'
     

     )
   • If you have more than three fractional digits you’ll need to trim them down (ADF only supports up to 3ms digits). You can use the substring trick from the docs:

     
          toTimestamp(
     
            substring($StartTimeString, 0, 23),
     
            'yyyy-MM-dd HH:mm:ss.SSS'
     
          )
     
     

3. A slightly simpler alternative: ISO-8601 + default parser
   • If you emit your pipeline date as ISO-8601 (e.g. 2026-04-24T13:18:34.698Z), then you can often call toTimestamp($StartTimeString) with no format and ADF will pick up the default "yyyy-[M]M-[d]d hh:mm:ss[.f...]" pattern.

Putting it all together:

• In your pipeline, change the format string to match Java’s pattern (or just output ISO-8601).

• In your Data Flow, treat the param as a string, then in the Source → Filter by last modified (Start time) box put:

toTimestamp(

  substring($StartTime, 0, 23),

  'yyyy-MM-dd HH:mm:ss.SSS'

)

That will give you a real timestamp you can feed into the blob filter.

Reference Docs

• Data flow expressions & toTimestamp() usage (Java patterns): https://learn.microsoft.com/azure/data-factory/data-flow-expression-functions#toTimestamp
• Milliseconds truncation workaround (substring + .SSS): https://learn.microsoft.com/azure/data-factory/dataflow-troubleshoot-guide#resolve-truncated-milliseconds-in-azure-data-factory-dataflow-datetime-processing
• Blob source “Filter by last modified” property: https://learn.microsoft.com/azure/data-factory/connector-azure-blob-storage#mapping-data-flow-properties

Hope this helps. If you have any follow-up questions, please let me know. I would be happy to help.

Answer 2

toTimestamp() in mapping data flows follows the Java SimpleDateFormat rules and the default pattern yyyy-[M]M-[d]d hh:mm:ss[.f...] when no format is supplied. In the provided expression, the format string does not match what toTimestamp() expects, so the parse fails and the function returns NULL.

Key points from the documented behavior:

• toTimestamp(<string>, [<timestamp format>], [<time zone>]) converts only from string to timestamp.
• If a format is supplied, it must be a valid SimpleDateFormat pattern.
• The function supports up to millisecond precision (.SSS).

In the working examples, the format strings are explicitly escaped and aligned with the input, for example:

toTimestamp('2019-02-03 05:19:28.871', 'yyyy-MM-dd HH:mm:ss.SSS')

and

millisecond(toTimestamp('2019-02-03 05:19:28.871', 'yyyy-MM-dd HH:mm:ss.SSS')) -> 871

To make the conversion work for the pipeline parameter:

1. Ensure the pipeline formats the value exactly as the data flow expects, including milliseconds:

      @formatDateTime(<date>, 'yyyy-MM-dd HH:mm:ss.fff')
   

2. In the data flow, declare StartTime as a string parameter.
3. Use a toTimestamp() call with a matching format pattern:

      toTimestamp($StartTime, 'yyyy-MM-dd HH:mm:ss.SSS')
   

4. Use this expression directly in the Filter by last modified → Start time (UTC) field, or assign it to a derived column and reference that expression.

Because the format string now matches both the actual value and the documented pattern, toTimestamp() will return a non‑NULL timestamp that can be used by the Blob source “Filter by last modified” setting.

---

References:

• Data transformation expression usage in mapping data flows
• Date and time functions in mapping data flows
• Copy and transform data in Azure Blob Storage by using Azure Data Factory or Azure Synapse Analytics

AI-generated content may be incorrect. Read our transparency notes for more information.