I asked this question regarding how to compare datetime2 values, but ignoring the nanoseconds. Since there is some rounding involved, it fails to compare the values properly.
In java, I can get a Long-value from Date and Timestamp which represents the milliseconds since January 1, 1970, 00:00:00 GMT. This is a pretty safe way to compare dates in java.
Is there a way to either
A) Get a numeric value from a datetime2 value which on represents the amount of milliseconds
B) Alter the rounding from the milliseconds to round down instead of up.
Example values:
2018-01-24 16:20:51.0715460
2018-01-25 09:52:04.1946950
2018-01-25 09:52:04.1946950
should match with
2018-01-24 16:20:51.0710000
2018-01-25 09:52:04.1940000
2018-01-25 09:52:04.1940000
Currently I am using cast(date as datetime2(3)) to receive the most precise result, but they still don't match due to rounding.
Can anyone give me a convenient way to compare the values above to match?
One quick option is to convert to varchar(23) which will essentially truncate the value
Example
Declare @YourTable Table ([D1] datetime2)
Insert Into @YourTable Values
('2018-01-24 16:20:51.0715460')
,('2018-01-25 09:52:04.1946950')
,('2018-01-25 09:52:04.1946950')
Select *
,NewVal = convert(varchar(23),D1)+'0000'
from @YourTable
Returns
D1 NewVal
2018-01-24 16:20:51.0715460 2018-01-24 16:20:51.0710000
2018-01-25 09:52:04.1946950 2018-01-25 09:52:04.1940000
2018-01-25 09:52:04.1946950 2018-01-25 09:52:04.1940000