SQL ignore nanoseconds when comparing datetime2

问题:

I have 2 tables with a datetime2 field each. One of the tables has stored nanoseconds as well, while the other one only stores up to milliseconds (there is no way to change this data). Now I want to compare the 2 tables and check if the datetime2 values match, but I have to ignore the nanoseconds for this.

Sample data:

Table1                         Table2

2018-01-24 10:51:23.9976180    2018-01-24 10:51:23.9970000
2018-01-24 10:51:23.9976180    2018-01-24 10:51:23.9970000
2018-01-24 11:08:23.2578500    2018-01-24 11:08:23.2570000

my query currently looks like this:

select t1.* from Table1 t1, Table12 t2 where t1.tradeDateTime = t2.tradeDate

As you can guess, I am not getting any results for these example, because the nanoseconds differ.

So my question is, how can I ignore the nanoseconds in the where-clause?

Edit:

I know that I should use proper JOINs. This query was just to test if I made no mistakes in my query using JOIN

回答1:

This all depends on what precision you want to work with. Do you want to match just the date portion or the date and time without milliseconds?

Here's a few ways to change the precision of the data:

CREATE TABLE #dates ( val DATETIME2 );

INSERT INTO #dates ( val ) VALUES ( SYSDATETIME());   
INSERT INTO #dates ( val ) VALUES ( SYSDATETIME());    
INSERT INTO #dates ( val ) VALUES ( SYSDATETIME());

SELECT d.val AsOriginalValue ,
       CAST(d.val AS DATETIME) AsDateTime ,
       CAST(d.val AS DATETIME2(0)) AsDateTime2_0 ,
       CAST(d.val AS DATE) AsDate
FROM   #dates AS d;

DROP TABLE #dates;

Results in:

AsOriginalValue             AsDateTime              AsDateTime2_0             AsDate
--------------------------- ----------------------- ---------------------- ----------
2018-01-25 14:35:14.3660549 2018-01-25 14:35:14.367 2018-01-25 14:35:14    2018-01-25
2018-01-25 14:35:14.3665552 2018-01-25 14:35:14.367 2018-01-25 14:35:14    2018-01-25
2018-01-25 14:35:14.3670555 2018-01-25 14:35:14.367 2018-01-25 14:35:14    2018-01-25

NOTE: beware of rounding.

回答2:

Try a CAST to DATETIME

select t1.* from Table1 t1, Table12 t2 
where CAST(t1.tradeDateTime as DATETIME) = CAST(t2.tradeDate as DATETIME)

A test

DECLARE @1 DATETIME2='2018-01-24 10:51:23.9976180'
, @2 DATETIME2 = '2018-01-24 10:51:23.9970000'


SELECT @1, @2,  Test =CASE WHEN CAST(@1 AS DATETIME) = CAST(@2 AS
DATETIME) THEN 1 ELSE 0 END

回答3:

Cast the dates as DateTime as this will cast the value to only 3 decimal places. Also, please use prober JOINS:

SELECT t1.*
FROM Table1 t1
    JOIN Table12 t2 ON t2.Joinable_ID = t1.Joinable_ID
WHERE CAST(t1.tradeDateTime AS DATETIME) = CAST(t2.tradeDate AS DATETIME)

回答4:

Using functions in the WHERE clause can cause performance problems. You might get better speed with a CTE approach:

 with t1 as 
           (select cast(tradeDateTime as datetime) as tradeDateTimeNoTime
            from Table1)
    , t2 as 
           (select cast(tradeDate as datetime) as tradeDateNoTime 
            from Table2)

 select t1.*
      , t2.* 
 from t1 
 inner join t2 
 on t1.tradeDateTimeNoTime = t2.tradeDateNoTime

Whichever solution you use, I would definitely recommend using the explicit JOIN syntax and not the comma syntax. Final note, that joining on a datetime field isn't ideal to begin with so you may want to consider an approach that uses IDs in both tables.