SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 datetime
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

peace
Constraint Violating Yak Guru

403 Posts

Posted - 07/16/2013 :  02:34:12  Show Profile  Reply with Quote

I would like to get something from joining to tableB.

The datetime in tableA are milisecond different from tableB.

How can I compare the joining?

TableA date: 2013-07-14 00:11:33.157
tableB date: 2013-07-14 00:12:02.157

If I join as below I wont get the result.

select * from tableA
inner join tableB on tableA.date=tableB.date

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/16/2013 :  04:25:21  Show Profile  Reply with Quote
you need to have another column based on which you need to relate tables. Otherwise how will you determine which record is to be related to which record?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 07/16/2013 :  06:18:50  Show Profile  Reply with Quote
quote:
Originally posted by peace


I would like to get something from joining to tableB.

The datetime in tableA are milisecond different from tableB.

How can I compare the joining?

TableA date: 2013-07-14 00:11:33.157
tableB date: 2013-07-14 00:12:02.157

If I join as below I wont get the result.

select * from tableA
inner join tableB on tableA.date=tableB.date

In the example you have given the two dates are not just a few milliseconds away, they are nearly 30 seconds away. Is that really the case, or are the times close enough and differ only by a few milliseconds? If they differ only by a few milliseconds, remove the millisecond part and join like shown below. The thing you have to be careful about is that when you do that, it may not always result in a one join:
select * from tableA a
inner join tableB b on 
DATEADD(ms,-DATEPART(ms,a.date),a.date) = DATEADD(ms,-DATEPART(ms,b.date),b.date)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/16/2013 :  06:50:51  Show Profile  Reply with Quote
Or is it that you'll have single entry per day in both tables with different timepart and you just need to relate between them?
if yes you could simply use logic like

select * from tableA a
inner join tableB b on 
WHERE DATEDIFF(dd,0,a.[date])=DATEADD(dd,0,b.[date])


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30177 Posts

Posted - 07/16/2013 :  07:47:11  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SELECT      *
FROM        dbo.Table1 AS t1
INNER JOIN  dbo.Table2 AS t2 ON DATEDIFF(SECOND, t1.[Date], t2.[Date]) BETWEEN -30 AND 30
to allow for +/- 30 seconds difference.



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

peace
Constraint Violating Yak Guru

403 Posts

Posted - 07/16/2013 :  22:54:16  Show Profile  Reply with Quote
how to + - within 24 hours?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/17/2013 :  01:15:16  Show Profile  Reply with Quote

select * from tableA a
inner join tableB b on 
WHERE b.[date] >= DATEADD(hh,-24,a.date)
AND b.[date] < = DATEADD(hh,24,a.date)



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000