SQL Server - date comparison in SQL - Asked By gibs on 10-Sep-14 07:07 AM

I have a table with about 250,000 records where one of the columns is of type datetime. If I execute a query like:

select lcdregnumber, lcddateissued from table1
where lcddateissued >= '20140401' and lcddateissued <= '20140831'
and (lcdRegNumber not in (select lcdRegNumber from tblLicenseDetails where lcdExpDate >= dateadd(day, 1, '20140831'))

The query execute successfully and takes less than 5 seconds to display the results. however, if I use  the value for the date as a variable the query takes very long to execute, for example up to 6 minutes (I cant work with such a time if I am to run the query from an application).

For example the query i am executing is:

declare @exp datetime

select @exp = '20140430'

select l.lcdregnumber, lcdNumber, l.lcdExpDate
from tblLicenseDetails
where ExpDate <= @exp
and (lcdRegNumber not in (select lcdRegNumber from tblLicenseDetails where lcdExpDate >= dateadd(day, 1, @exp))

A record for lcdRegNumber can appear more than once in the table but with different values for lcdExpdate.

How best can I speed up my query so that if I am to put this code in a stored procedure and execute it from an application, I do not get the 'Timeout expired' error?

Is ExpDate in an index?  The two queries you show here are different.  So comparing their performance is useless.

I like to store dates as numbers. It is a simple thing to convert a date to a number and back and searching on a number field that is indexed is as fast as you can get -  almost better than a potato in a microwave!

Example: June 12, 2014 - 20140612

Now get to work!