CODEWITHSUNDEEP
sqlt-sqldate
lolxor
lolxor

Reputation: 55

Efficient use of current date in view

I have a view that needs to show data for the past 60 days. Currently I have written:

WHERE (TNP.ATimeWindowEnd > '2015-07-18 00:00:00') AND (TNP.ATimeWindowEnd < '2015-09-16 00:00:00')

But in order to not edit this manually every time I tried using DateAdd(Day, Datediff(Day,0, GetDate() -1), 0) (and -61) in place of the static dates. This worked but slowed the performance of the query considerably making the view unpractical. I then moved the date function to a variable as:

SET @ToDate = DateAdd(Day, Datediff(Day,0, GetDate() -1), 0)

This improved speed of the query (I suspect it only runs once instead of once per line) but now I cannot use it in a view since I can't add local variables to views. Any ideas on how to get dynamic dates into a view without affecting the performance?

Upvotes: 2

Views: 139

Answers (2)

Utsav
Utsav

Reputation: 8103

Can you use below in the view? Please check.

WHERE TNP.ATimeWindowEnd > DATEADD(day, -60, CURRENT_TIMESTAMP)

Edit: Replaced < with >

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270653

If you have an index on TNP(ATimeWindowEnd), then using a function can change the index usage. SQL Server generally does not support the use of indexes on functions, so you can use:

WHERE TNP.ATimeWindowEnd >= CAST(DATEADD(day, -60, CURRENT_TIMESTAMP) as DATE)

All the computation is done on the CURRENT_TIMESTAMP. The time component is removed by the CAST().

If you are using an older version of SQL Server, then you need to use the DATEADD() trick to remove the time component.

Upvotes: 0

Related Questions