SQL Server Performance Forum – Threads Archive

Filter DateTimeValue from a DateRange

Hi, below 2 query will return back same result, just the question which 1 will be better
in terms of performance ? If i execute the same query in SQL2000, is that the same ? Just assume in jaJobLog contain 1 million record and got a secondary index as
"CheckInDateTime, UserCode". The Primary key is TrxID. Field "CheckInDateTime" is DateTime
Field and Value contain time portion, eg 2007-04-13 9:45PM. Select * from dbo.jaJobLog
Where CheckInDateTime between ‘2007-04-12’ and dbo.cfStrToDate(‘2007-04-12’) + 1
and UserCode = ‘U1001’ Select * from dbo.jaJobLog
Where dbo.cfDatetimeToDate(CheckInDateTime) between ‘2007-04-12’ and ‘2007-04-12’
and UserCode = ‘U1001’ * I have look at the Execution plan in SQL2005, both query are equal to cost 50% and
use Cluster Index scan ( might due to only got less than 100 record in my current table).
Since i got a secondary index which meet the above ‘where’ condition, can i force SQL server to use the index instead of Cluster Index ? ////////////////////////////////////
//Detail of user define function
//////////////////////////////////// ALTER FUNCTION [dbo].[cfDateTimeToDate](@dtDate AS DateTime)
RETURNS DateTime AS
BEGIN
RETURN(Convert(DateTime, Convert( varchar(50), @dtDate, 112)))
END ALTER FUNCTION [dbo].[cfStrToDate](@vcDate AS varchar(10))
RETURNS DateTime AS
BEGIN
RETURN Convert(DateTime, @vcDate)
END

---

First query is better and do not use functions against columns in where clause…
I don’t see any reason to use function… Select * from dbo.jaJobLog
Where CheckInDateTime between ‘2007-04-12’ and dbo.cfStrToDate(‘2007-04-12’) + 1
and UserCode = ‘U1001’
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

the cfStrToDate is redundant. SQL Server knows how to convert from string to datetime
Select * from dbo.jaJobLog
Where CheckInDateTime between ‘2007-04-12’ and ‘2007-04-13’
and UserCode = ‘U1001’ or Select * from dbo.jaJobLog
Where CheckInDateTime between ‘2007-04-12’ and dateadd(day, 1, ‘2007-04-12’)
and UserCode = ‘U1001’ KH

---

http://sql-server-performance.com/fk_datetime.asp Madhivanan Failing to plan is Planning to fail

---

]]>