2

My company is preparing a from-scratch new version of an existing product, and one of the changes we're making is to switch from SQL Server 2005 to 2008. We're currently trying to decide whether we should switch from datetime to datetime2 for columns that hold date & time information.

We know datetime2 is (strongly) recommended, but some of our customers have their own SQL Server databases (version unknown) that they run side-by-side with our product's database, and they sometimes pull data from our database to use with their own database operations.

Obviously we're risking cast errors if they attempt to store a datetime2 with too much precision from our database in a datetime column in their database, but we're also wondering if there might be problems with comparisons. The behavior of comparing floats to doubles is somewhat non-intuitive in a lot of programming languages; would there be similar problems in comparing datetime values to datetime2 values?

Oblivious Sage
  • 3,326
  • 6
  • 37
  • 58

2 Answers2

1

Yes, it can, due to a breaking change in SQL Server 2016:

select cast('2020-08-11 14:50:50.123' as datetime2) AS DT2,
    cast('2020-08-11 14:50:50.123' as datetime) AS DT,
    cast(cast('2020-08-11 14:50:50.123' as datetime) as datetime2) AS ConvertedDT2,
    case when cast('2020-08-11 14:50:50.123' as datetime) = cast('2020-08-11 14:50:50.123' as datetime) then 1 else 0 end AS DateTimeEqualsDateTime,
    case when cast('2020-08-11 14:50:50.123' as datetime2) = cast('2020-08-11 14:50:50.123' as datetime2) then 1 else 0 end AS DateTime2EqualsDateTime2,
    case when cast('2020-08-11 14:50:50.123' as datetime2) = cast('2020-08-11 14:50:50.123' as datetime) then 1 else 0 end AS DateTime2EqualsDateTime,
    case when cast(cast('2020-08-11 14:50:50.123' as datetime) as datetime2) = cast('2020-08-11 14:50:50.123' as datetime2) then 1 else 0 end AS ConvertedDateTime2EqualsDateTime2

Results: Results

You can see how increasing the precision by converting a datetime to a datetime2 leaves us with non-zero artifacts in some cases. That makes it so that equality checks won't always work correctly between datetime2 and datetime.

StriplingWarrior
  • 151,543
  • 27
  • 246
  • 315
0

I'm not an expert in this area but I just ran a quick select to see if they would return as being equal...

SELECT 
  isEqual = CASE WHEN CONVERT(DATETIME2, GETDATE()) = CONVERT(DATETIME, GETDATE()) THEN 1 ELSE 0 END
  ,Date2 = CONVERT(DATETIME2, GETDATE())
  ,Date1 = CONVERT(DATETIME, GETDATE())

I assume you will get the same result as me, mine returned a 1 so it thinks they are the same. This is obviously a less than perfect science since I'm sending the exact same number in as apposed to being a few milliseconds off. Although since DATETIME doesn't capture this I wonder if that would even make a difference.

Kyle Mac
  • 146
  • 6
  • 1
    In later versions of SQL Server the rules for converting datetime to datetime2 have changed. This will now sometimes return zero. – David Dubois Jul 12 '19 at 13:31