Handling Null and Blank Date Fields in Power Automate

Checking whether a date field is blank in Power Automate has always been trickier than it should be. The value might be null, an empty string, or the infamous 0001-01-01T00:00:00Z. Here is how to handle all of these reliably.

The Problem

When you pull a date field from Dataverse, SharePoint, or a SQL connector, an empty date can appear in multiple forms depending on the source:

• Dataverse: returns null
• SharePoint: returns an empty string ""
• SQL Server: may return 1900-01-01 or NULL
• Excel: returns an empty string or 0

A simple equals null condition often misses these edge cases.

The Reliable Approach

Use the empty() function combined with coalesce():

@empty(coalesce(triggerOutputs()?['body/dateField'], ''))

This handles both null and empty string in a single expression. If the field is null, coalesce falls back to an empty string, and empty() returns true.

Checking for the Epoch Date

Some connectors return 0001-01-01T00:00:00Z instead of null. Add a secondary check:

@or(
  empty(coalesce(triggerOutputs()?['body/dateField'], '')),
  equals(formatDateTime(triggerOutputs()?['body/dateField'], 'yyyy'), '0001')
)

A Cleaner Pattern with Compose

For readability, break it into steps:

1. Compose — name it DateValue:

   @coalesce(triggerOutputs()?['body/dateField'], '')
   

2. Condition — check if blank:

   @or(empty(outputs('DateValue')), equals(outputs('DateValue'), '0001-01-01T00:00:00Z'))
   

This makes the flow easier to debug and the logic visible at a glance.

Filter at the Trigger, Not After

If the flow should not run when the date is empty, do not detect it in a Condition action. Use Filter rows on the Dataverse trigger instead:

datefield eq null

For SharePoint list triggers, a trigger condition is the equivalent:

@not(empty(triggerOutputs()?['body/DateField']))

Skipping the run is cheaper than starting it and terminating. It also keeps the run history readable.

Dataverse-Specific: Use the Null Check Directly

With the current Dataverse connector, you can use the condition action and compare directly to null — it now handles this correctly for Dataverse date fields. Select the field, operator is equal to, and leave the value blank.

This works for Dataverse. It still fails for SharePoint empty strings, Excel serial 0 / 1899-12-30, and SQL 1900-01-01. If the flow might be reused across connectors, keep empty(coalesce(...)).

Key Takeaway

Never trust a single null check for dates across different connectors. The empty(coalesce(...)) pattern is defensive, portable, and takes 30 seconds to implement. Use it everywhere.