Optimized Query Methods for Retrieving Last Month Records in SQL Server

Introduction

In database application development, querying data by time range is a common requirement. Particularly in scenarios such as member management and order statistics, there is often a need to retrieve records from specific time periods. This article focuses on how to efficiently and accurately obtain records from the previous month in SQL Server.

Problem Analysis

Assume we have a member table (Member) containing a date creation field (date_created). Users need to query all records from the previous month. For example, if the current date is January 3, 2010, the query should return all records from December 1, 2009 to December 31, 2009.

While this requirement appears straightforward, practical implementation requires consideration of multiple factors: precise handling of date boundaries, query performance optimization, and proper handling of cross-year scenarios.

Core Solution

The DATEPART function-based query method provides the most direct and effective solution. This approach filters records precisely by comparing month and year values:

SELECT * 
FROM Member
WHERE DATEPART(m, date_created) = DATEPART(m, DATEADD(m, -1, getdate()))
AND DATEPART(yyyy, date_created) = DATEPART(yyyy, DATEADD(m, -1, getdate()))

The core logic of this query includes:

• Using DATEADD(m, -1, getdate()) to obtain the date from the previous month
• Extracting month information via DATEPART(m, ...)
• Extracting year information via DATEPART(yyyy, ...)
• Precisely matching the month and year of table records with the previous month's month and year

Technical Details Analysis

DATEPART Function Detailed Explanation

The DATEPART function extracts specified date parts from datetime values. In our query, we utilize:

• DATEPART(m, date_created): Extracts the month from the record creation date
• DATEPART(yyyy, date_created): Extracts the year from the record creation date
• DATEPART(m, DATEADD(m, -1, getdate())): Extracts the month from the previous month
• DATEPART(yyyy, DATEADD(m, -1, getdate())): Extracts the year from the previous month

Cross-Year Handling Mechanism

This solution automatically handles special cross-year scenarios. When the current month is January, DATEADD(m, -1, getdate()) automatically returns the date from December of the previous year, ensuring correct year comparison.

Performance Optimization Considerations

While the DATEPART function-based query offers clear logic, performance considerations are important:

When functions are applied to columns in query conditions, the database optimizer may not effectively utilize indexes on those columns. This means query performance could be impacted for large tables.

To verify query effectiveness, we can create test data:

create table #test(id int, date_created datetime);
insert into #test values
(1, '2023-01-06'),
(2, '2023-02-01'),
(3, '2023-02-12'),
(4, '2023-02-15'),
(5, '2023-03-07'),
(6, '2022-12-10'),
(7, '2022-12-20');

Applying our query solution:

SELECT * FROM #test
WHERE DATEPART(m, date_created) = DATEPART(m, DATEADD(m, -1, getdate()))
AND DATEPART(yyyy, date_created) = DATEPART(yyyy, DATEADD(m, -1, getdate()))

This will correctly return all records from the previous month.

Alternative Approach Comparison

Another common method uses date range queries:

DECLARE @startOfCurrentMonth DATETIME
SET @startOfCurrentMonth = DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0)

SELECT *
FROM Member
WHERE date_created >= DATEADD(month, -1, @startOfCurrentMonth)
      AND date_created < @startOfCurrentMonth

This approach offers advantages including:

• Better utilization of indexes on date fields
• Avoidance of function application to columns in query conditions
• More precise date boundary handling

However, for simple previous month query requirements, the DATEPART-based method is more intuitive and easier to understand.

Practical Application Recommendations

When selecting a query approach, consider the following factors:

1. Data Volume: For small tables, performance differences between methods are minimal; for large tables, date range queries are recommended to leverage indexes
2. Query Frequency: In high-frequency query scenarios, prioritize performance-optimized solutions
3. Development and Maintenance Costs: DATEPART-based methods offer simple logic that is easy to understand and maintain
4. Business Requirement Complexity: For more complex time range queries, date range methods offer better extensibility

Conclusion

Retrieving last month records is a common query requirement in SQL Server. The DATEPART function-based query approach provides an intuitive and effective solution, particularly suitable for small to medium-sized data tables and simple business scenarios. By deeply understanding date function characteristics and query optimization principles, developers can select the most appropriate implementation based on specific requirements.

In practical applications, it is recommended to choose the appropriate query method based on specific data scale, performance requirements, and business complexity, conducting performance testing and optimization when necessary.