SQL Query on Date function to fetch rows in history dynamically

Question

Team, I am looking for a date function that when i use it in procedures, it should automatically get the desired range looking at parameter. ex: query should show me results from today's date to 90 days in history. like

select * from Table
where dataFunction(currDate, 90)

where currDate should consider todays date and pull me all the rows for past 90 days.

am using MSSMS v17.5

Answer 1

Don't write functions for this. They don't play well with indexes, which cuts to the core of database performance. You need to use the built-in items in the WHERE clause... something more like this:

select * 
from Table
where Table.DateColumn BETWEEN DATEADD(d, -90, current_timestamp) AND current_timestamp

If you really want to use just the date, instead of the full DateTime value, then cast as date:

select * 
from Table
where Table.DateColumn BETWEEN DATEADD(d, -90, cast(current_timestamp as date)) AND cast(current_timestamp as date)