SQL Server Select Date Older Than 30 Days

by

In this comprehensive article, I’ll share the most effective methods I’ve used to select records with dates older than 30 days, including performance optimization techniques and real-world applications.

SQL Server Select Date Older Than 30 Days

Before diving into the technical implementation, let me explain the common business scenarios where this functionality becomes crucial:

  • Data Archiving: Moving old transaction records to archive tables for performance optimization
  • Compliance Requirements: Identifying records for retention policy enforcement
  • Performance Maintenance: Cleaning up temporary data and log files
  • Business Analytics: Analyzing trends beyond the current month
  • Audit Trails: Reviewing historical changes and modifications

Method 1: Using DATEADD() Function

The DATEADD() function is my preferred method for date comparisons because it’s index-friendly and provides optimal performance.

Basic Syntax and Implementation

-- Basic syntax for selecting records older than 30 days
SELECT *
FROM TableName
WHERE DateColumn < DATEADD(DAY, -30, GETDATE())

Practical Example: Customer Orders

Here’s how I implement this in real business scenarios:

SELECT *
FROM SalesData
WHERE OrderDate < DATEADD(DAY, -30, GETDATE())

After executing the above query, I got the expected output as shown in the screenshot below.

SQL Server Select Date Older Than 30 Days

Advanced Example with Multiple Conditions

-- Select orders older than 30 days with additional criteria
SELECT 
    o.OrderID,
    c.CustomerName,
    c.State,
    o.OrderDate,
    o.TotalAmount,
    DATEDIFF(DAY, o.OrderDate, GETDATE()) AS DaysOld
FROM Sales.Orders o
INNER JOIN Customers.CustomerInfo c ON o.CustomerID = c.CustomerID
WHERE o.OrderDate < DATEADD(DAY, -30, GETDATE())
    AND c.State IN ('California', 'Texas', 'New York', 'Florida')
    AND o.OrderStatus = 'Completed'
ORDER BY o.OrderDate DESC;

Method 2: Using DATEDIFF() Function

While the DATEDIFF(). The function provides excellent readability, but it’s less efficient for large datasets because it must evaluate the function for every row.

Basic Implementation

SELECT 
 Salesid,
    DATEDIFF(DAY, OrderDate, GETDATE()) AS DaysSinceOrder
FROM SalesData
WHERE DATEDIFF(DAY, OrderDate, GETDATE()) > 30
ORDER BY OrderDate DESC;

After executing the above query, I got the expected output as shown in the below screenshot.

SQL Select Date Older Than 30 Days

When to Use DATEDIFF()

I typically use DATEDIFF() when:

  • Working with small datasets (under 10,000 rows)
  • The readability of the query is more important than performance
  • I need to display the exact number of days in the result set
  • Creating ad-hoc queries for analysis

Method 3: Using Specific Date Calculations – For Complex Scenarios

Sometimes business requirements demand more specific date handling, especially when dealing with business days, weekends, or specific calendar dates.

Business Days Calculation

-- Select records older than 30 business days (excluding weekends)
DECLARE @BusinessDaysBack INT = 30;
DECLARE @CalendarDaysBack INT = @BusinessDaysBack + (@BusinessDaysBack / 5) * 2; -- Approximate weekend adjustment

SELECT 
    TransactionID,
    CustomerName,
    TransactionDate,
    Amount,
    TransactionType
FROM Finance.Transactions
WHERE TransactionDate < DATEADD(DAY, -@CalendarDaysBack, GETDATE())
    AND DATEPART(WEEKDAY, TransactionDate) NOT IN (1, 7) -- Exclude Sunday and Saturday
ORDER BY TransactionDate DESC;

Month-End Specific requirements

-- Select records from more than 30 days ago, but only month-end dates
SELECT 
    ReportID,
    ReportName,
    GeneratedDate,
    ReportType
FROM Reports.MonthlyReports
WHERE GeneratedDate < DATEADD(DAY, -30, GETDATE())
    AND GeneratedDate = EOMONTH(GeneratedDate) -- Only month-end dates
ORDER BY GeneratedDate DESC;

Method 4: Using Date Ranges for Better Performance

For optimal performance with large datasets, you can use date ranges instead of open-ended queries, as mentioned below:

-- Define specific date range for better query performance
DECLARE @EndDate DATETIME = DATEADD(DAY, -30, GETDATE());
DECLARE @StartDate DATETIME = DATEADD(MONTH, -12, @EndDate); -- Last 12 months of old data

SELECT 
    LogID,
    UserName,
    ActionPerformed,
    LogDate,
    IPAddress
FROM Security.UserLogs
WHERE LogDate >= @StartDate 
    AND LogDate < @EndDate
ORDER BY LogDate DESC;

Conclusion

Selecting records with dates older than 30 days is sometimes critical for certain requirements. In this article, I have explained 4 simple methods to achieve this requirement.

You may also like the following articles.