0

Behaviour of column type 'DateTime2' between SQL 2014 and SQL 2022. This was found when our application's calculation for datetime difference of columns being type DateTime2 went wrong.

Tried to find from google if there would be a specific setting in server property for datetime2 type of columns, but no luck.

Noticed - DateTime2 improvements It is stated the behaviour is expected. Datetime2 with SQL 2014 is of precision 7 with only first 3 digits updated whereas its now 7 with SQL 2022 updated for all 7 digits. And so calculations with the datetime2 fields are turning mess after upgrading.

Now, there are lot of columns in many tables which were defined as datetime2. It would take time to fix correction to all queries, application codes.

Will there be any simple fix to this?

Meera K
  • 81
  • 7

2 Answers2

1

The simplest fix is to cast or convert DateTime2 columns to a uniform precision as DATETIME2(3) in your queries, however, this is short-term. You can use the CONVERT or CAST function to mimic SQL Server 2014 functions. An example:

SELECT DATEDIFF(MILLISECOND,

CAST(Column1 AS DATETIME2(3)),

CAST(Column5 AS DATETIME2(3))) AS DiffMilliseconds

FROM YourTable;

This will ensure consistency and avoid application code changes as you adapt gradually.

Severalnines
  • 419
  • 1
  • 5
1

Will there be any simple fix to this?

The only 'simple fix' is to run the database at a compatibility level below 130 until your code is ready for the behavioural changes.

For example:

ALTER DATABASE YourDB SET COMPATIBILITY_LEVEL = 120;

You will find your issue goes away.

Of course, you will miss out on facilities only available at 130 (and above). A summary is available at Differences between compatibility level 120 and level 130 as a starting point.

Related Q & A: Incorrect comparison of datetime and datetime2
My article: Don't Mix with Datetime

An earlier version of your question did have a code demo. You were inserting into a datetime2 column using select getdate() as a source—a function which returns datetime not datetime2.

That's where the conversion is happening and why the situation I describe—comparing datetime to datetime2—is relevant.

Paul White
  • 94,921
  • 30
  • 437
  • 687