Sql Server: Get data for only the past year

sql

date-filtering

performance

dynamic-queries

byAlex Kataev·Aug 20, 2024

Fetch data from the last year using DATEADD paired with GETDATE():

SELECT * FROM your_table
WHERE your_date_column >= DATEADD(year, -1, GETDATE());

This snippet discards all rows with dates in your_date_column older than a year from the present date.

Time Zone and Format Aware

We need to account for time zones and enforce date formatting if our SQL Server operates across diverse global regions or demands a specific date format:

SELECT *
FROM your_table
WHERE your_date_column >= CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, your_date_column), DATENAME(TzOffset, SYSDATETIMEOFFSET())))
AND your_date_column < CONVERT(datetime, GETDATE());

In this code, the SWITCHOFFSET function manages time zone offsets and ensures we're comparing accurate current times, irrespective of server location.

Dynamic and Comprehensive Queries

To design more sophisticated and practical SQL queries, it is smarter to create dynamic range queries without any manual adjustments:

SELECT *
FROM your_table
WHERE your_date_column >= CAST(DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AS DATE) - 365;

This statement ensures the exact previous year's data extraction, encompassing complete 365 days from the start of the day, not just the past 365 days.

Visualising the Process

Visualising the task like pruning a tree, the objective is retaining the fresh branches (data from the last year):

🌳 All data (over time):
| 🍁 2019 | 🍂 2020 | 🍃 2021 | 🌿 2022 | 🌱 2023 |

SQL Pruning Tool (WHERE clause):
| 🪓 WHERE DateColumn >= DATEADD(YEAR, -1, GETDATE()) |

🌴 Recent data (past year):
| 🌿 2022 | 🌱 2023 |

Key Point: The SQL query keeps the database populated only with the significant recent data from the past year, similar to pruning to maintain a tree's fresh growth.

Striking a Balance: Performance and Readability

While creating your SQL queries, striving for balance between performance and readability is crucial.

-- Only the best of the best data survives this rigorous cut! 🏆🪓
SELECT *
FROM your_table WITH (INDEX(IX_your_date_column)) -- Speedy Gonzales encourages indexing for speed! 🚀 
WHERE your_date_column >= DATEADD(year, -1, CAST(GETDATE() AS date));

In this code, ensuring the date column you're filtering is indexed can dramatically boost your query's speed. Besides, including comments can quell future confusion!

Deciphering Date Filters

While working with dates, it's crucial to distinguish between data from the "past 365 days" and from the "previous calendar year" (Jan 1 to Dec 31 of last year):

-- Abracadabra 🎩🐇... and voila, data from the last calendar year!
SELECT *
FROM your_table
WHERE YEAR(your_date_column) = YEAR(DATEADD(year, -1, GETDATE()));