DATE comparison bug - v12.0.32.3340
jmeyer
Posts: 70 Bronze 2
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;
Comments
I have responded via email to get more information about what you are seeing.
Kind Regards,
Technical Sales Engineer
Redgate Software