Join on 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

SQL Server 2000 Forums

Transact-SQL (2000)

Join on Datetime

Author

Topic

Sim
Starting Member

12 Posts

Posted - 2006-03-14 : 16:01:31

I want to join two tables where: Table1.Datetime=Table2.Datetime on year, month, and day. I don't want hour, minutes, etc included.

What is the format for this? I am new to Enterprise/Query Analyzer.

vito1281
Starting Member

12 Posts

Posted - 2006-03-14 : 16:22:39

How about:


SELECT...
...WHERE YEAR(Table1.Datetime) = YEAR(Table2.Datetime)
      AND MONTH(Table1.Datetime) = MONTH(Table2.Datetime)
      AND DAY(Table1.Datetime) = DAY(Table2.Datetime)

This is probably the cleanest way. You can also generate "timeless" dates from the existing dates (using casting) and compare the converted values, but the casting code would make it a bit uglier.

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-03-14 : 17:16:27

I'm not sure that's the "cleanest" way. That performs three comparisons instead of potentially one:

WHERE CONVERT(char(10), Table1.Datetime, 101) = CONVERT(char(10), Table2.Datetime, 101)

But then, I didn't do any testing to see which is actually faster.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-14 : 19:48:49

If one of the tables has an index on the datetime column, you might be able to get it to use that index for the join by not converting that date, and converting the date in the next table to beginning of day and beginning of the next day, and then joining on those.

Normally, SQL Server will not use an index on a column if you are using a function on that column in your join.

If you don't have an index on a date, that won't matter, but you could still try this method and the others to see which is faster.

This code is a example of joining this way.


select
	a.col1,
	b.col2
from
	TableWithIndexedDate a
	join
	TableB b
	on
	a.IndexedDate >=
	-- Start of day at midnight for TableB date
	dateadd(dd,datediff(dd,0,b.Date),0)	and
	a.IndexedDate <
	-- Start of next day at midnight for TableB date
	dateadd(dd,datediff(dd,0,b.Date)+1,0)

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-15 : 01:57:34

Just for clarity I used to use day instead of dd


select
	a.col1,
	b.col2
from
	TableWithIndexedDate a
join
	TableB b
on
	a.IndexedDate >=dateadd(day,datediff(day,0,b.Date),0)	
	and
	a.IndexedDate <	dateadd(day,datediff(day,0,b.Date),1)

Madhivanan

Failing to plan is Planning to fail

Sim
Starting Member

12 Posts

Posted - 2006-03-15 : 14:34:11

Thank you very much. You've renewed my faith in humanity

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-16 : 01:34:04

Also read this to know more about querying on dates
http://www.sql-server-performance.com/fk_datetime.asp

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources