Joining on datetimes but ignoring the time part
-
Hi,
I need to join two tables using datetime fields, but i only want to join using the date part dd/mm/yyyy. In otherwords I want to ignore times, so that 01/01/2001 nn:nn:nn = 01/01/2001 nn:nn:nn where n is any time.
I have written a select statement which joins by using the convert function to convert both datetimes to a varchar(10) dd/mm/yyyy. I know this is a terrible way to join tables but I have no other choice at this stage. As you would guess the select is very slow as I cannot use an index for the convert expression.
Is there a better way I can join these tables on date, hopefully using indexes??
Thanks!
-
not sure this will be better than convert(), but can give it a try
select dateadd(day, 0, datediff(day, 0, getdate()))
-
As you are aware, performing just about any calculation on an indexed column will negate any chance of using the index. Try this... it's simple and makes for nasty fast returns...
DECLARE @FindDate DATETIME
SET @FindDate = '01/01/2001'
SELECT *
FROM yourtable
WHERE somedatecol >= @FindDate
AND somedatecol < @FindDate + 1
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
I don't think there's a good way to join tables on dates, ignoring times and utilizing an index.
I'd look at alternative solutions if this is something you'll be doing regularly. You could add a date only datetime field to each table and index. Not ideal, but...
-
Dang... didn't read the post close enough... my apologies for the previous lamer post.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Thanks for all the posts.
Luckily I only need to run this query once a month, but having a separate indexed date column seems the best approach.
Thanks all!
-
You could use the Convert function to format the date as a string which does not include the time:
e.g.
WHERE Convert(varchar,table1.date,103) = Convert(varchar,table2.date,103)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply