When comparing date-time data types SQL DataCompare first converts certain date-time data types to VARBINARY(MAX) when selecting the data int he source/destination database instead of retaining the original data types.
-- set up reproducible bug table structure and data
USE tempdb;
GO
SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[RedGateTableDATE]')
AND type IN (N'U') )
DROP TABLE dbo.RedGateTableDATE;
GO
CREATE TABLE dbo.RedGateTableDATE (RedGateTableDATEID INT IDENTITY(1, 1)
NOT NULL
, loaddateDTM2 DATETIME2 NOT NULL
, loaddateDTM DATETIME NOT NULL
, loaddateDTMO DATETIMEOFFSET NOT NULL
, loaddateSDTM SMALLDATETIME NOT NULL
, loaddateDT DATE NOT NULL
, loaddateTM TIME NOT NULL
, CONSTRAINT PK_dbo_RedGateTableDATE PRIMARY KEY CLUSTERED (RedGateTableDATEID ASC) ON [PRIMARY])
ON
[PRIMARY];
GO
DECLARE @MaxNumber INT = 1000000;
WITH Pass0
AS (SELECT 1 AS C
UNION ALL
SELECT 1) , --2 rows
Pass1
AS (SELECT 1 AS C
FROM Pass0 AS A
, Pass0 AS B) ,--4 rows
Pass2
AS (SELECT 1 AS C
FROM Pass1 AS A
, Pass1 AS B) ,--16 rows
Pass3
AS (SELECT 1 AS C
FROM Pass2 AS A
, Pass2 AS B) ,--256 rows
Pass4
AS (SELECT 1 AS C
FROM Pass3 AS A
, Pass3 AS B) ,--65536 rows
Pass5
AS (SELECT 1 AS C
FROM Pass4 AS A
, Pass4 AS B) ,--4,294,967,296 rows
Tally
AS (SELECT ROW_NUMBER() OVER (ORDER BY Pass5.C) AS Number
FROM Pass5)
INSERT INTO dbo.RedGateTableDATE
(loaddateDTM2
, loaddateDTM
, loaddateDTMO
, loaddateSDTM
, loaddateDT
, loaddateTM
)
SELECT CAST(GETDATE() AS DATETIME2) AS loaddateDTM2
, CAST(GETDATE() AS DATETIME) AS loaddateDTM
, CAST(GETDATE() AS DATETIMEOFFSET) AS loaddateDTMO
, CAST(GETDATE() AS SMALLDATETIME) AS loaddateSDTM
, CAST(GETDATE() AS DATE) AS loaddateDT
, CAST(GETDATE() AS TIME) AS loaddateTM
FROM Tally t
WHERE t.Number <= @MaxNumber;
-- display the data
SELECT TOP 100
t.RedGateTableDATEID
, t.loaddateDTM2
, t.loaddateDTM
, t.loaddateDTMO
, t.loaddateSDTM
, t.loaddateDT
, t.loaddateTM
FROM dbo.RedGateTableDATE t;
-- SELECT query run by SQL Datacompare 12.0.32.3340 - force binary collation on/off does not make a difference
SELECT RedGateTableDATEID
, CAST (loaddateDTM2 AS VARBINARY(MAX))
, loaddateDTM
, CAST (loaddateDTMO AS VARBINARY(MAX))
, loaddateSDTM
, CAST (loaddateDT AS VARBINARY(MAX))
, CAST (loaddateTM AS VARBINARY(MAX))
FROM dbo.RedGateTableDATE WITH (NOLOCK)
ORDER BY RedGateTableDATEID;
Thanks for your post jmeyer,
-- set up reproducible bug table structure and data USE tempdb; GO SET ANSI_NULLS, QUOTED_IDENTIFIER ON; GO IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RedGateTableDATE]') AND type IN (N'U') ) DROP TABLE dbo.RedGateTableDATE; GO CREATE TABLE dbo.RedGateTableDATE (RedGateTableDATEID INT IDENTITY(1, 1) NOT NULL , loaddateDTM2 DATETIME2 NOT NULL , loaddateDTM DATETIME NOT NULL , loaddateDTMO DATETIMEOFFSET NOT NULL , loaddateSDTM SMALLDATETIME NOT NULL , loaddateDT DATE NOT NULL , loaddateTM TIME NOT NULL , CONSTRAINT PK_dbo_RedGateTableDATE PRIMARY KEY CLUSTERED (RedGateTableDATEID ASC) ON [PRIMARY]) ON [PRIMARY]; GO DECLARE @MaxNumber INT = 1000000; WITH Pass0 AS (SELECT 1 AS C UNION ALL SELECT 1) , --2 rows Pass1 AS (SELECT 1 AS C FROM Pass0 AS A , Pass0 AS B) ,--4 rows Pass2 AS (SELECT 1 AS C FROM Pass1 AS A , Pass1 AS B) ,--16 rows Pass3 AS (SELECT 1 AS C FROM Pass2 AS A , Pass2 AS B) ,--256 rows Pass4 AS (SELECT 1 AS C FROM Pass3 AS A , Pass3 AS B) ,--65536 rows Pass5 AS (SELECT 1 AS C FROM Pass4 AS A , Pass4 AS B) ,--4,294,967,296 rows Tally AS (SELECT ROW_NUMBER() OVER (ORDER BY Pass5.C) AS Number FROM Pass5) INSERT INTO dbo.RedGateTableDATE (loaddateDTM2 , loaddateDTM , loaddateDTMO , loaddateSDTM , loaddateDT , loaddateTM ) SELECT CAST(GETDATE() AS DATETIME2) AS loaddateDTM2 , CAST(GETDATE() AS DATETIME) AS loaddateDTM , CAST(GETDATE() AS DATETIMEOFFSET) AS loaddateDTMO , CAST(GETDATE() AS SMALLDATETIME) AS loaddateSDTM , CAST(GETDATE() AS DATE) AS loaddateDT , CAST(GETDATE() AS TIME) AS loaddateTM FROM Tally t WHERE t.Number <= @MaxNumber; -- display the data SELECT TOP 100 t.RedGateTableDATEID , t.loaddateDTM2 , t.loaddateDTM , t.loaddateDTMO , t.loaddateSDTM , t.loaddateDT , t.loaddateTM FROM dbo.RedGateTableDATE t; -- SELECT query run by SQL Datacompare 12.0.32.3340 - force binary collation on/off does not make a difference SELECT RedGateTableDATEID , CAST (loaddateDTM2 AS VARBINARY(MAX)) , loaddateDTM , CAST (loaddateDTMO AS VARBINARY(MAX)) , loaddateSDTM , CAST (loaddateDT AS VARBINARY(MAX)) , CAST (loaddateTM AS VARBINARY(MAX)) FROM dbo.RedGateTableDATE WITH (NOLOCK) ORDER BY RedGateTableDATEID;