Why does sys.fn_xe_file_target_read_file require an explicit cast on datetime2 column?

According to the documentation the returned column timestamp_utc should be of type datetime2(7)

But when I query like this
SELECT
*
FROM sys.fn_xe_file_target_read_file('system_health*.xel', null, null, null)
WHERE timestamp_utc > dateadd(hour, -1, GETUTCDATE())


It returns no rows. It returns the rows only when I add an explicit cast to datetime2
SELECT
*
FROM sys.fn_xe_file_target_read_file('system_health*.xel', null, null, null)
WHERE cast(timestamp_utc as datetime2(7)) > dateadd(hour, -1, GETUTCDATE())


Which matches the last example in the documentation (even though no attention is drawn to it)

Why is that?

Erland Sommarskog asked me about this in May 2022.

It's a bug exposed when a predicate is pushed down into the internal streaming table-valued function. The return type is indeed datetime2(7) but the source is a Windows FILETIME.

The predicate is pushed down below the internal conversion, so the server ends up trying to compare a datetime2 with a FILETIME, which does not work.

Pushed down predicate

When you add the explicit conversion, the predicate can no longer be pushed into the TVF so it appears as a separate Filter:

Predicate evaluated in a separate filter

I have no idea why this hasn't been fixed.

Erland subsequently created a feedback item: Filtering output from fn_xe_file_target_read_file on timestamp_utc returns no rows

StackExchange Database Administrators Q#323147, answer score: 13