Comparing effective dates in SQL

Question

Wondering if there is a better why in the WHERE clause of choosing records when you need to look at effective start and end dates?

Currently this how I've done it in the past on MS SQL Server. Just worried about the date and not the time. I'm using SQL Server 2005.

AND CAST(CONVERT( CHAR(10), ep.EffectiveStartDate, 101) AS DATETIME) <= CAST( CONVERT( CHAR(10), GETDATE(), 101) AS DATETIME) AND CAST(CONVERT( CHAR(10), ep.EffectiveEndDate, 101) AS DATETIME) >= CAST( CONVERT( CHAR(10), GETDATE(), 101) AS DATETIME)

Answer 1

try

ep.EffectiveStartDate BETWEEN @date1 AND @date2

where you would do something like

declare @date1 datetime, @date2 datetime;  
set @date1 = cast('10/1/2000' as datetime)  
set @date2 = cast('10/1/2020' as datetime)

Answer 2

That is terrible, take a look at Only In A Database Can You Get 1000% + Improvement By Changing A Few Lines Of Code to see how you can optimize this since that is not sargable

Also check out Get Datetime Without Time and Query Optimizations With Dates

Answer 3

@Darren Kopp

Be carefull with BETWEEN, check out How Does Between Work With Dates In SQL Server?

Answer 4

@Darren Kopp - you can use

set @date2 = '20201001'

this will let you lose the cast.

footndale - you can use date arithmetic to remove the time as well. Something like

select dateadd(d, datediff(d, 0, CURRENT_TIMESTAMP), 0)

to get today's date (without the time). I believe this is more efficient than casting back and forth.