# Optimize where clause using dates

I'm trying to optimize several queries that all use a similar pattern on one of the `WHERE` clauses:

``````AND (DATEADD(DAY
, ISNULL(a.[due_days], 30) + 30
, [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL))
) < GETDATE()
``````

The `CalcDate` udf based on the `type` field value makes some comparisons and returns a date. Then adds an amount of days to that date and compares to current date. In order to be able to use an existing index on `due_days` I want to transform the operation to apply all the transformations to `GETDATE()`, let's say I want to make it `sargable`, if possible. Also, if there is some recommendation on what can be done to improve the use of the udf even better.

It's not so hard to do this transformation. Step by step:

``````DATEADD(DAY
, ISNULL(a.[due_days], 30) + 30
, [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
) < GETDATE()
``````

means:

``````[dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
+ (ISNULL(a.[due_days], 30) + 30) DAYS
< GETDATE()
``````

then we have to break the `ISNULL()` into 2 cases:

``````    [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
+ (a.[due_days] + 30) DAYS
< GETDATE()
OR
a.[due_days] IS NULL
AND
[dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
+ (30 + 30) DAYS
< GETDATE()
``````

which can be written as:

``````    (a.[due_days] + 30) DAYS
< GETDATE() - [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
OR
a.[due_days] IS NULL
AND
(30 + 30) DAYS
< GETDATE() - [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
``````

so we can use `DATEDIFF()`:

``````    (a.[due_days] + 30)
< DATEDIFF( day
, [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
, GETDATE()
)
OR
a.[due_days] IS NULL
AND
(30 + 30)
< DATEDIFF( day
, [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
, GETDATE()
)
``````

and finally:

``````    a.[due_days]
< DATEDIFF( day
, [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
, GETDATE()
) - 30
OR
a.[due_days] IS NULL
AND
30
< DATEDIFF( day
, [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
, GETDATE()
) - 30
``````

Corrected, taking care of the time parts:

``````    a.[due_days]
< DATEDIFF( day
, [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
, GETDATE()
) - 30
, DATEDIFF( day
, [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
, GETDATE()
)
, [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
) > GETDATE()
THEN 1 ELSE 0
END
OR
a.[due_days] IS NULL
AND
30
< DATEDIFF( day
, [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
, GETDATE()
) - 30
, DATEDIFF( day
, [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
, GETDATE()
)
, [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
) > GETDATE()
THEN 1 ELSE 0
END
``````

You could simplify it a bit, with the use of `CROSS APPLY`:

``````CROSS APPLY
( SELECT gdt = GETDATE(),
calc = [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
) AS c
CROSS APPLY
( SELECT diff = x.diff - CASE WHEN DATEADD( day, x.diff, c.calc ) > c.gdt
THEN 1 ELSE 0
END
FROM
( SELECT diff = DATEDIFF( day, c.calc, c.gdt) - 30
) AS x
) AS y
----
WHERE (  a.[due_days] < y.diff
OR  a.[due_days] IS NULL  AND  30 < y.diff
)
``````
• Tried same approach, but later when testing didn't got same results when running the query. Will try it right now, thx Apr 30, 2014 at 11:49
• @Yaroslav: Yeah, there is a difference due to how `DATEDIFF` works. I will try to correct it. (A related question: are you sure you want to use `GETDATE()` (the current timestamp) and not the current date in your query?) Apr 30, 2014 at 11:58
• No, I can surely use just current date. As mentioned on the question, while the result remains the same, any recommendation is welcomed Apr 30, 2014 at 12:03
• Does the `dbo.CalcDate()` return dates (without time part) or datetimes? Apr 30, 2014 at 12:04
• Returns datetime. And can not be changed as it is used on several others sp's and udf's. And as I'm still new with database structure and schema don't want to change it to avoid breaking something else in the process Apr 30, 2014 at 12:07