I try to always make my queries sargable but there's an issue that creeps up routinely when I'm working with various application databases (that I have not created and cannot change).
The issue is that sometimes we'll have two columns that store dates, but the dates are not both date or datetime data types. I can easily do the conversions to get them to the same data type for comparison, but then it is no longer sargable.
I'd like to see if anyone has advice (besides 'change the data types! )
Here's a sample:
DECLARE @BadTable TABLE ( id int primary key identity not null ,IntegerDate INT --Bad date field ,EventDate DATE --Good date field )
INSERT INTO @BadTable (IntegerDate, EventDate)
--Look for bad date inserts
SELECT Id, IntegerDate, EventDate
WHERE CONVERT(DATE, CONVERT(CHAR(8), IntegerDate,112))<>EventDate