Cannot Filter by DateTime Type in Azure SQL Cross-Database Queries

Question

In Azure SQL Database, we use external tables to query data from another Azure SQL database. When filtering an external table by a DATETIME column, no rows are returned if the final digit of the milliseconds is 3 or 7. If the final digit is 0, the query works as expected.

Example:

/* MILLISECONDS ENDS WITH 0 - WORKS AS EXPECTED*/
DECLARE @myDate DATETIME = '2021-10-27 12:00:00.000'; 
SELECT * from dbo.ext_datetimetest where myDate = @myDate;
GO

/* MILLISECONDS ENDS WITH 3 OR 7 - RETURNS NOTHING*/
DECLARE @myDate DATETIME = '2021-10-27 12:00:00.003'; 
SELECT * from dbo.ext_datetimetest where myDate = @myDate;
GO

Notes:

• Only paramerized queries are affected. Any query with the datetime value hardcoded in the where clause works as expected.
• Only cross-database queries are affected. Runnning the queries directly on the source database works as expected.
• Our code has been working as expected for years and just started this behavior last week.
• This is only happening in our Test and Prod environments. Our Dev environment works as expected for all datetime values.

Steps to Reproduce:

/* EXECUTE IN DATABASE #1 */
CREATE TABLE dbo.datetimetest (myDate DATETIME NOT NULL);
GO
INSERT dbo.datetimetest (myDate)
VALUES 
     ('2021-10-27 12:00:00.000')
    ,('2021-10-27 12:00:00.003')
    ,('2021-10-27 12:00:00.007')
    ,('2021-10-27 12:00:00.010')
;
GO

/* EXECUTE IN DATABASE #2 */
CREATE EXTERNAL TABLE dbo.ext_datetimetest ( myDate DATETIME NOT NULL)
WITH (DATA_SOURCE = [DATABASE #1], SCHEMA_NAME = N'dbo', OBJECT_NAME = N'datetimetest');
GO

/* SELECT ALL ROWS TO CONFIRM VALUES */
SELECT * FROM dbo.ext_datetimetest;

/* These all work because the filters are hardcoded */
SELECT * from dbo.ext_datetimetest where myDate = '2021-10-27 12:00:00.000';
SELECT * from dbo.ext_datetimetest where myDate = '2021-10-27 12:00:00.003';
SELECT * from dbo.ext_datetimetest where myDate = '2021-10-27 12:00:00.007';
SELECT * from dbo.ext_datetimetest where myDate = '2021-10-27 12:00:00.010';
GO

/* VARIABLES ONLY WORK IF LAST DIGIT IS 0 */
DECLARE @myDate DATETIME;
SET @myDate = '2021-10-27 12:00:00.000'; SELECT * from dbo.ext_datetimetest where myDate = @myDate; /* WORKS */
SET @myDate = '2021-10-27 12:00:00.003'; SELECT * from dbo.ext_datetimetest where myDate = @myDate; /* RETURNS NOTHING */
SET @myDate = '2021-10-27 12:00:00.007'; SELECT * from dbo.ext_datetimetest where myDate = @myDate; /* RETURNS NOTHING */
SET @myDate = '2021-10-27 12:00:00.010'; SELECT * from dbo.ext_datetimetest where myDate = @myDate; /* WORKS */
GO

Answer 1

As a possible workaround (not a fix to the root problem), try:

... where myDate = CONVERT(VARCHAR(23), @myDate, 21)

I am speculating that the date value is being converted to some representation on the cross database connection that doesn't precisely match the 1/300 sec precision needed for an exact comparison. Converting to a string may pass the equivalent to the literal (string) date that works. Efficiency may be compromised though.