SQL Server: Get Data for Only the Past Year

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?
• Querying Data for the Past Year
  • Using the GETDATE() Function
  • Example Query
• Handling Different Date Columns
  • Filtering by DATE vs. DATETIME
    • Accounting for Time Zones
• Optimizing Performance for Large Datasets
  • Indexing on Date Columns
  • Using a Range Specifier (< and >=)
• Conclusion
• FAQs

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

Optimizing Performance for Large Datasets

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

WHERE Date_Column IS NOT NULL

CREATE VIEW LastYearData AS

SELECT *

FROM Intellipaat_Table

WHERE Date_Column >= DATEADD(YEAR, -1, GETDATE());