Fetching Past Year's Data from Your SQL Server Tables

• SQL Server provides the YEAR() function to extract the year portion from a date or datetime column.

Filtering based on Current Date:

• To get data for the past year, you need to compare the extracted year with the current year.

WHERE Clause for Filtering:

• Once you have the comparison criteria (extracted year vs. desired year), you can use the WHERE clause in your SQL statement to filter the data. The WHERE clause specifies the condition for selecting rows.

Here's an example:

SELECT *
FROM MyTable
WHERE YEAR(MyDateColumn) = 2023  -- Fixed Date (Replace 2023 with your desired year)

-- OR

SELECT *
FROM MyTable
WHERE YEAR(MyDateColumn) = YEAR(GETDATE()) - 1  -- Dynamic Date (Past Year)

In this example:

• MyTable is the table containing your data.
• MyDateColumn is the column that stores the date or datetime values.
• The first query retrieves data where the year in MyDateColumn is exactly 2023 (replace with your specific year).
• The second query retrieves data where the year in MyDateColumn matches the current year minus one (i.e., the past year).

This code retrieves data from the table Sales where the SaleDate falls within the year 2023.

SELECT *
FROM Sales
WHERE YEAR(SaleDate) = 2023;

Example 2: Dynamic Date (Past Year)

This code retrieves data from the table Orders where the OrderDate falls within the past year relative to the current date.

SELECT *
FROM Orders
WHERE YEAR(OrderDate) = YEAR(GETDATE()) - 1;

Example 3: Specific Columns and Past Year (Dynamic)

SELECT CustomerID, ProductID, Quantity
FROM OrderDetails
WHERE YEAR(OrderDate) = YEAR(GETDATE()) - 1;

Note:

• Replace Sales, Orders, OrderDetails, SaleDate, and OrderDate with the actual names of your table and date columns.
• You can modify these examples further to suit your specific needs. For instance, you can add additional filtering conditions in the WHERE clause or change the columns being selected.

Instead of directly comparing years, you can use the BETWEEN clause to specify a date range. This can be helpful if you want to include the entire past year, including the current day.

SELECT *
FROM MyTable
WHERE MyDateColumn BETWEEN DATEADD(year, -1, GETDATE()) AND GETDATE();

This query uses DATEADD function to subtract one year from the current date and then uses BETWEEN to filter between that date and the current date (GETDATE()).

DATEPART Function:

Another way to extract specific parts of a date is with the DATEPART function. It offers more flexibility than YEAR().

SELECT *
FROM MyTable
WHERE DATEPART(year, MyDateColumn) = YEAR(GETDATE()) - 1;

Here, DATEPART(year, MyDateColumn) extracts the year from MyDateColumn, and the rest of the logic remains similar to the previous methods.

WHERE Clause with Expressions:

You can leverage expressions within the WHERE clause to achieve dynamic filtering.

SELECT *
FROM MyTable
WHERE MyDateColumn >= DATEADD(year, -1, GETDATE());

This approach filters data where MyDateColumn is greater than or equal to one year before the current date, effectively retrieving data for the past year (including today).

Choosing the Right Method:

• The method you choose depends on your preference and the specific needs of your query.
• If you simply want data for a fixed year, directly comparing years with YEAR() is straightforward.
• For dynamic filtering based on the current date, GETDATE() and subtractions are useful.
• The BETWEEN clause offers an alternative for specifying a date range.
• DATEPART provides more granular control over date extraction if needed.