There can be many such instances where you might face the situation, in which you are required to retrieve data only for the past year. In those cases, this GETDATE() function comes in very handy. This article will help you know how you could use SQL for fetching data for the past year.
Table of Contents
What is Date Filtering in SQL Server?
SQL Server offers a set of built-in date functions, which are important for filtering data based on time intervals. These include:
- GETDATE(): Returns the system date and time.
- DATEADD(): Adds or subtracts a specified time interval to a date.
- CONVERT(): Converts data between different date formats.
Querying Data for the Past Year
1. Using the GETDATE() and DATEADD()
To get records of the past year, you might use GETDATE() to pick up the present date and then DATEADD() to get that date minus a year.
Example
You can try querying for records to filter like this:
SELECT *
FROM Intellipaat_Table
WHERE Date_Column>= DATEADD(YEAR, -1, GETDATE());
How this works:
- DATEADD(YEAR, -1, GETDATE()): Calculating the date from one year ago from today.
- Date_Column>= DATEADD(YEAR, -1, GETDATE()): Filters to only rows that have dates within the last year.
Handling DATETIME Columns
Filtering Data by Date When Using DATETIME Columns
If your column is of type DATETIME, then it contains both date and time. Make sure your filtering logic takes this into account.
For detailed filtering:
SELECT *
FROM Intellipaat_Table
WHERE CAST(Data_Column AS DATE) >= CAST(DATEADD(YEAR, -1, GETDATE()) AS DATE);
Case: Accounting for Different Time Zones
If your data spans several time zones, convert all timestamps to UTC before filtering for consistency. Use GETUTCDATE() instead of GETDATE() for time zone-aware filtering.
SELECT *
FROM Intellipaat_Table
WHERE CAST(Data_Column AT TIME ZONE 'UTC' AS DATE) >= CAST(DATEADD(YEAR, -1, GETUTCDATE()) AS DATE);
In the above example, CAST convert
1. Indexing on Date Columns
Make sure that Date_Columns is indexed to improve your query performance. Without an index, this may need a full table scan.
2. Using a Range Specifier (< and >=)
The other best practise to optimize performance for large dataset is to define a specific date range. The query is given below:
SELECT *
FROM Intellipaat_Table
WHERE Date_Column >= DATEADD(YEAR, -1, GETDATE())
AND Date_Column < GETDATE();
This query retrieves all records from Intellipaat_Table where the Date_Column is within the last year but not including current date.
Conclusion
Getting only data for last year in SQL Server is quite a common need, which is easily done by using functions such as GETDATE() and DATEADD(). Adequate indexing, along with proper knowledge of the date types used, will get your queries up and running well even on a large dataset. If you want to learn such similar and interesting concepts, then you should head to our advanced SQL Certification Course.
FAQs
1. What happens if the Date_Column contains null values?
You can remove null values from a column by adding a condition there:
WHERE Date_Column IS NOT NULL
2. Can I use this query with a different time interval, like months or days?
Yes, change the interval of the DATEADD function:
- For last month: DATEADD(MONTH, -1, GETDATE())
- For last 7 days: DATEADD(DAY, -7, GETDATE())
3. Does this query consider time portions in DATETIME columns?
Yes, it includes time. Use CAST(Date_Column AS DATE) to filter by date only.
4. How can I apply this logic to a SQL Server view?
Create a view with the filter condition:
CREATE VIEW LastYearData AS
SELECT *
FROM Intellipaat_Table
WHERE Date_Column >= DATEADD(YEAR, -1, GETDATE());
5. Is there a way to automate this for periodic reporting?
Yes, you can schedule the query in SQL Server Agent, or you can integrate it into reporting tools like Power BI or SSRS.