datetime - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

datetime

Author

Topic

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-07-16 : 02:34:12

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

52326 Posts

Posted - 2013-07-16 : 04:25:21

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-16 : 06:18:50

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)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-16 : 06:50:51

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-16 : 07:47:11

[code]SELECT *
FROM dbo.Table1 AS t1
INNER JOIN dbo.Table2 AS t2 ON DATEDIFF(SECOND, t1.[Date], t2.[Date]) BETWEEN -30 AND 30[/code]to allow for +/- 30 seconds difference.

N 56°04'39.26"
E 12°55'05.63"

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-07-16 : 22:54:16

how to + - within 24 hours?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-17 : 01:15:16

[code]
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)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources