May 31, 2017 at 7:05 am
I am trying to join these two tables based on date only, for instance, 05-03-17
SELECT top 5 CreateDate
FROM [dbo].[Tracking]
JOIN [dbo].[SalesOrderXmit] ON CreateDate=Entered
Above is returning no set at all, of course.
How can I accomplish that, without torpedoing any existing Index? I know I must use a function in order to get rid of the hr, ss, ms, etc.
May 31, 2017 at 7:13 am
Please post the DDL for the tables involved, sample data and expected output. It will help the experts here to help you out.
May 31, 2017 at 7:19 am
TheSQL_fan - Wednesday, May 31, 2017 7:05 AMI am trying to join these two tables based on date only, for instance, 05-03-17
SELECT top 5 CreateDate
FROM [dbo].[Tracking]
JOIN [dbo].[SalesOrderXmit] ON CreateDate=EnteredAbove is returning no set at all, of course.
How can I accomplish that, without torpedoing any existing Index? I know I must use a function in order to get rid of the hr, ss, ms, etc.
SELECT TOP(5) x.CreateDate
FROM [dbo].[Tracking] t
JOIN [dbo].[SalesOrderXmit] x
ON CAST(x.CreateDate AS DATE) = CAST(t.Entered AS DATE)
ORDER BY [shouting]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 31, 2017 at 7:31 am
ChrisM@Work - Wednesday, May 31, 2017 7:19 AMTheSQL_fan - Wednesday, May 31, 2017 7:05 AMI am trying to join these two tables based on date only, for instance, 05-03-17
SELECT top 5 CreateDate
FROM [dbo].[Tracking]
JOIN [dbo].[SalesOrderXmit] ON CreateDate=EnteredAbove is returning no set at all, of course.
How can I accomplish that, without torpedoing any existing Index? I know I must use a function in order to get rid of the hr, ss, ms, etc.
SELECT TOP(5) x.CreateDate
FROM [dbo].[Tracking] t
JOIN [dbo].[SalesOrderXmit] x
ON CAST(x.CreateDate AS DATE) = CAST(t.Entered AS DATE)
ORDER BY [shouting]
Thanks Chris
That's what I had. So this won't torpedo any Index I may have on the joining attributes? Because the CAST function?
May 31, 2017 at 7:34 am
TheSQL_fan - Wednesday, May 31, 2017 7:31 AMChrisM@Work - Wednesday, May 31, 2017 7:19 AMTheSQL_fan - Wednesday, May 31, 2017 7:05 AMI am trying to join these two tables based on date only, for instance, 05-03-17
SELECT top 5 CreateDate
FROM [dbo].[Tracking]
JOIN [dbo].[SalesOrderXmit] ON CreateDate=EnteredAbove is returning no set at all, of course.
How can I accomplish that, without torpedoing any existing Index? I know I must use a function in order to get rid of the hr, ss, ms, etc.
SELECT TOP(5) x.CreateDate
FROM [dbo].[Tracking] t
JOIN [dbo].[SalesOrderXmit] x
ON CAST(x.CreateDate AS DATE) = CAST(t.Entered AS DATE)
ORDER BY [shouting]Thanks Chris
That's what I had. So this won't torpedo any Index I may have on the joining attributes? Because the CAST function?
I'm fairly sure that casting from datetime to date is SARGable on supported versions of SQL Server. Glance at your execution plan to confirm.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 31, 2017 at 7:46 am
I am using SQL2008, but I will take a look on the actual execution plan. Thank you!
May 31, 2017 at 7:56 am
TheSQL_fan - Wednesday, May 31, 2017 7:31 AMThanks ChrisThat's what I had. So this won't torpedo any Index I may have on the joining attributes? Because the CAST function?
Under normal circumstances, yes, using a function on a table column in the search predicate would affect SARGability. The CAST/CONVERT from datetime to date is a rare "exception to the rule".
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply