0

I am attempting to round DateTime to DateTime2, and I was able to round it down to DateTime is rounding to .157, while DateTime2 is rounding to .157000, but DateTime still does not equal DateTme2 when comparing. This is my code:

DECLARE @dt2 datetime2(7),
@dt datetime;
SET @dt2 = '2016-07-11T13:50:11.157';

SET @dt = @dt2;

IF @dt = @dt2
PRINT 'The Same (Implicit);

I have also tried:

declare @dt2 datetime2(7) = '2016-07-11T13:50:11.157'
    declare @dt datetime
    set @dt = @dt2


SELECT
@dt2 AS [Datetime2 value]
, @dt AS [Datetime value]
, CONVERT(DATETIME2,@dt) AS [Datetime converted to Datetime2]
, CONVERT(DATETIME2,@dt2) AS [Datetime2 converted to Datetime2]
, CONVERT(DATETIME,@dt) AS [Datetime converted to Datetime]
, CONVERT(DATETIME,@dt2) AS [Datetime2 converted to Datetime]`

and in the results table it shows:

DateTime2 Value - 2016-07-11 13:50:11.1570000
DateTime value - 2016-07-11 13:50:11.157
Datetime to DateTime2 - 2016-07-11 13:50:11.1566667
desertnaut
  • 57,590
  • 26
  • 140
  • 166
Tyler Antoni
  • 1
  • 1
  • What is the *actual* problem and why do you want these to match? `datetime` is essentially a floating point and therefore *not* precise. Use `datetime2` throughout if you want consistent results – Panagiotis Kanavos Oct 08 '21 at 16:46
  • The previous developers setup the tables with DateTime, and the software is sending the data over as DateTime2. We can't change the code, or the SQL columns, and we do not have this issue on any of our other servers (which are running the same version of SQL Server) – Tyler Antoni Oct 08 '21 at 16:53
  • But what is the actual "problem" or "issue"? Are you trying to retrieve a row based on a datetime/datetime2 value that was generated in the app and stored in the database? Why are you trying to identify rows this way? – Aaron Bertrand Oct 08 '21 at 17:00
  • This is documented [here](https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetime2-transact-sql?view=sql-server-ver15#converting-other-date-and-time-types-to-the-datetime2-data-type): "Under database compatibility level 130, implicit conversions from datetime to datetime2 data types show improved accuracy by accounting for the fractional milliseconds, resulting in different converted values [..]. Use explicit casting to datetime2 datatype whenever a mixed comparison scenario between datetime and datetime2 datatypes exists." – Jeroen Mostert Oct 08 '21 at 17:01
  • It furthermore links to [this support article](https://learn.microsoft.com/troubleshoot/sql/database-design/sql-server-azure-sql-database-improvements) for more. The bottom line is: you don't want to be relying on implicit conversions, and if you are, now would be an excellent time to change that. If you can't, reverting the compatibility level may be an option, but that can bring its own difficulties of course. (A lower compat level is the likely explanation for why you don't see this behavior on the other servers.) – Jeroen Mostert Oct 08 '21 at 17:03
  • 1
    If you want millisecond accuracy use `datetime2(3)`. Again, a `datetime` is a floating point number. The fractional part represents the time of day and as the docs explain it's [Rounded to increments of .000, .003, or .007 seconds](https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-ver15). `157` isn't such an increment – Panagiotis Kanavos Oct 08 '21 at 17:03
  • Thank you to everyone who replied! I realize it was sort of an empty question, but the compat level was the issue. Thank you @JeroenMostert for fixing our dumb issue! – Tyler Antoni Oct 08 '21 at 17:43

0 Answers0