<p>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?</p>

<p>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.</p>

<pre><code>AND Convert(datetime, Convert(char(10), ep.EffectiveStartDate, 101))
   &lt;= Convert(datetime, Convert(char(10), GetDate(), 101))
AND Convert(datetime, Convert(char(10), ep.EffectiveEndDate, 101))
   &gt;= Convert(datetime, Convert(char(10), GetDate(), 101))
</code></pre>

[{'answer_id': 22809, 'author': 'Darren Kopp', 'author_id': 77, 'author_profile': 'https://Stackoverflow.com/users/77', 'pm_score': -1, 'selected': False, 'text': "<p>try</p>\n\n<pre><code>ep.EffectiveStartDate BETWEEN @date1 AND @date2\n</code></pre>\n\n<p>where you would do something like</p>\n\n<pre><code>declare @date1 datetime, @date2 datetime;  \nset @date1 = cast('10/1/2000' as datetime)  \nset @date2 = cast('10/1/2020' as datetime)\n</code></pre>\n"}, {'answer_id': 22817, 'author': 'SQLMenace', 'author_id': 740, 'author_profile': 'https://Stackoverflow.com/users/740', 'pm_score': 1, 'selected': False, 'text': '<p>That is terrible, take a look at <a href="http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/only-in-a-database-can-you-get-1000-impr" rel="nofollow noreferrer">Only In A Database Can You Get 1000% + Improvement By Changing A Few Lines Of Code</a> to see how you can optimize this since that is not sargable</p>\n\n<p>Also check out <a href="http://wiki.lessthandot.com/index.php/Get_Datetime_Without_Time" rel="nofollow noreferrer">Get Datetime Without Time</a> and <a href="http://wiki.lessthandot.com/index.php/Query_Optimizations_With_Dates" rel="nofollow noreferrer">Query Optimizations With Dates</a></p>\n'}, {'answer_id': 22820, 'author': 'SQLMenace', 'author_id': 740, 'author_profile': 'https://Stackoverflow.com/users/740', 'pm_score': 0, 'selected': False, 'text': '<p>@Darren Kopp</p>\n\n<p>Be carefull with BETWEEN, check out <a href="http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/how-does-between-work-with-dates-in-sql-" rel="nofollow noreferrer">How Does Between Work With Dates In SQL Server?</a></p>\n'}, {'answer_id': 22824, 'author': 'AlexCuse', 'author_id': 794, 'author_profile': 'https://Stackoverflow.com/users/794', 'pm_score': 1, 'selected': False, 'text': "<p>@Darren Kopp - you can use </p>\n\n<pre><code>set @date2 = '20201001'\n</code></pre>\n\n<p>this will let you lose the cast.</p>\n\n<p>footndale - you can use date arithmetic to remove the time as well.  Something like </p>\n\n<pre><code>select dateadd(d, datediff(d, 0, CURRENT_TIMESTAMP), 0)\n</code></pre>\n\n<p>to get today's date (without the time).  I believe this is more efficient than casting back and forth.</p>\n"}, {'answer_id': 4799464, 'author': 'ErikE', 'author_id': 57611, 'author_profile': 'https://Stackoverflow.com/users/57611', 'pm_score': 0, 'selected': False, 'text': '<pre><code>AND DateDiff(Day, 0, GetDate()) + 1 &gt; ep.EffectiveStartDate\nAND DateDiff(Day, 0, GetDate()) &lt; ep.EffectiveEndDate\n</code></pre>\n\n<p>I think you will find that these conditions offer the best performance possible. This will happily utilize indexes.</p>\n\n<p>I am also very sure that this is right and will give the right data. No further calculation of dates without time portions is needed.</p>\n'}]
['https://Stackoverflow.com/questions/22807', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/2526/']