Making two date columns sargable

Transact-SQL
1

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! :slight_smile: )

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)
VALUES
(20151001, '2015-10-02')
,(20151020, '2015-10-20')
,(20151022, '2015-10-21')

--Look for bad date inserts
SELECT Id, IntegerDate, EventDate
FROM @BadTable
WHERE CONVERT(DATE, CONVERT(CHAR(8), IntegerDate,112))<>EventDate

Thanks!

2

flip the order in the WHERE

3

I thought that anytime you apply a function to a column in the table within the WHERE clause, regardless of placement, it will make it non-sargable. What I understood was that if you take a constant/literal and wrap it in a function instead of the column data, that would make it sargable. But when looking at two columns, it does not. That's not the case?

4

You can't sarg 'em both, you just have to pick one to convert. I'd convert the date to int and then compare as ints just to avoid any potential date conversion issues going the other way. But since they're in the same table, even sargability wouldn't likely help.

5

yes, you're right. missed the point about two columns in the same row. Other thing to try: add a persisted computed column to do the date conversion, then index it

6

Thanks gbritton...I'll keep that in mind for db's I develop but as I'd mentioned; it's an application database from a vendor and have no ability to change the schema. Appreciate the insights! Thanks (Thanks Scott as well)

7

I don't believe anything is going to help here because you need to look at the whole table anyway. A table scan is going to happen no matter what.