What are the challenges you face when working across database platforms? Take the survey
Options

DATE comparison bug - v12.0.32.3340

jmeyerjmeyer Posts: 70 Bronze 2
edited October 25, 2016 11:20AM in SQL Data Compare
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

  • Options
    Thanks for your post jmeyer,

    I have responded via email to get more information about what you are seeing.

    Kind Regards,
    Andrew Pierce
    Technical Sales Engineer
    Redgate Software
  • Options
    jmeyerjmeyer Posts: 70 Bronze 2
    Just for everybody's benefit, the code below the "SELECT query run by SQL DataCompare 12.0.32.3340" section is what SQL DataCompare runs against the SQL Server instance when it it is retrieving data for the comparison. It was retrieved via Adam Machanic's sp_WhoIsActive stored procedure.
  • Options
    jmeyerjmeyer Posts: 70 Bronze 2
    This item is resolved as the root cause is a Microsoft issue to begin with and Re-Gate works around this by utilizing VARBINARY(MAX) for the comparison of certain date-time data types.
Sign In or Register to comment.