How to Filter Records by Date in SQL Server (Last 7 Days, Current Month, Date Range)

 

Introduction to Date Filtering in SQL Server

Date filtering is one of the most common operations in database queries. SQL Server provides robust datetime functions and operators to filter records based on date criteria efficiently. Proper date filtering is crucial for performance, especially with large datasets.

1. Understanding SQL Server Date/Time Data Types

Before filtering, understand the available date/time types:

sql

-- Different date/time data types
CREATE TABLE DateExamples (
    ID INT IDENTITY PRIMARY KEY,
    EventDateTime DATETIME,        -- Pre-SQL Server 2008 (time rounded to .000, .003, .007 seconds)
    EventDateTime2 DATETIME2,      -- Higher precision (100 nanoseconds)
    EventDate DATE,                -- Date only (no time)
    EventTime TIME,                -- Time only (no date)
    EventDateTimeOffset DATETIMEOFFSET -- Date + time + timezone offset
);

INSERT INTO DateExamples (EventDateTime, EventDateTime2, EventDate, EventTime, EventDateTimeOffset)
VALUES (
    GETDATE(),
    SYSDATETIME(),
    CAST(GETDATE() AS DATE),
    CAST(GETDATE() AS TIME),
    SYSDATETIMEOFFSET()
);

2. Setting Up Sample Data

sql

-- Create sample orders table
CREATE TABLE Orders (
    OrderID INT IDENTITY PRIMARY KEY,
    CustomerID INT,
    OrderDate DATETIME,
    TotalAmount DECIMAL(10, 2),
    Status VARCHAR(20)
);

-- Insert sample data (last 30 days)
DECLARE @i INT = 0;
WHILE @i < 1000
BEGIN
    INSERT INTO Orders (CustomerID, OrderDate, TotalAmount, Status)
    VALUES (
        ABS(CHECKSUM(NEWID())) % 100 + 1,
        DATEADD(DAY, -ABS(CHECKSUM(NEWID())) % 30, GETDATE()),
        ABS(CHECKSUM(NEWID())) % 1000 + 1,
        CASE ABS(CHECKSUM(NEWID())) % 4 
            WHEN 0 THEN 'Pending' 
            WHEN 1 THEN 'Completed' 
            WHEN 2 THEN 'Shipped' 
            WHEN 3 THEN 'Cancelled' 
        END
    );
    SET @i = @i + 1;
END

-- Create index for performance
CREATE INDEX IX_Orders_OrderDate ON Orders(OrderDate);

3. Filtering by Specific Date Ranges

Basic Date Range Filtering

sql

-- Orders between two specific dates
SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM Orders
WHERE OrderDate >= '2023-10-01' AND OrderDate < '2023-11-01';

-- Using BETWEEN (be careful with time components)
SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM Orders
WHERE OrderDate BETWEEN '2023-10-01' AND '2023-10-31 23:59:59.997';

4. Filtering Relative to Current Date

Last 7 Days (including today)

sql

SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM Orders
WHERE OrderDate >= DATEADD(DAY, -6, CAST(GETDATE() AS DATE)) -- Start of 7 days ago
  AND OrderDate < DATEADD(DAY, 1, CAST(GETDATE() AS DATE));  -- Start of tomorrow

-- Alternative using DATEDIFF
SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM Orders
WHERE DATEDIFF(DAY, OrderDate, GETDATE()) <= 6;

Last 30 Days

sql

SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM Orders
WHERE OrderDate >= DATEADD(DAY, -29, CAST(GETDATE() AS DATE))
  AND OrderDate < DATEADD(DAY, 1, CAST(GETDATE() AS DATE));

Today's Records

sql

SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM Orders
WHERE OrderDate >= CAST(GETDATE() AS DATE)
  AND OrderDate < DATEADD(DAY, 1, CAST(GETDATE() AS DATE));

5. Filtering by Month and Year

Current Month

sql

SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM Orders
WHERE OrderDate >= DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
  AND OrderDate < DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1));

-- Using EOMONTH (SQL Server 2012+)
SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM Orders
WHERE OrderDate >= DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
  AND OrderDate < DATEADD(DAY, 1, EOMONTH(GETDATE()));

Specific Month and Year

sql

-- October 2023
SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM Orders
WHERE OrderDate >= '2023-10-01'
  AND OrderDate < '2023-11-01';

-- Using DATEFROMPARTS
SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM Orders
WHERE OrderDate >= DATEFROMPARTS(2023, 10, 1)
  AND OrderDate < DATEADD(MONTH, 1, DATEFROMPARTS(2023, 10, 1));

6. Filtering by Time Components

Today's Orders by Hour

sql

-- Orders from the last 4 hours
SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM Orders
WHERE OrderDate >= DATEADD(HOUR, -4, GETDATE())
  AND OrderDate < GETDATE();

-- Orders between specific hours today
SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM Orders
WHERE OrderDate >= CAST(GETDATE() AS DATE) + CAST('09:00:00' AS TIME)
  AND OrderDate < CAST(GETDATE() AS DATE) + CAST('17:00:00' AS TIME);

7. Advanced Date Filtering Techniques

Using Date Tables for Complex Filtering

sql

-- Create a date dimension table
CREATE TABLE DimDate (
    DateKey INT PRIMARY KEY, -- YYYYMMDD
    FullDate DATE,
    DayOfWeek INT,
    WeekNumber INT,
    MonthNumber INT,
    Quarter INT,
    Year INT,
    IsWeekday BIT,
    IsHoliday BIT
);

-- Join with date table for complex filtering
SELECT o.OrderID, o.OrderDate, o.TotalAmount, d.DayOfWeek
FROM Orders o
INNER JOIN DimDate d ON CAST(o.OrderDate AS DATE) = d.FullDate
WHERE d.Year = 2023
  AND d.Quarter = 4
  AND d.IsWeekday = 1;

Filtering by Day of Week

sql

-- Orders on weekends
SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM Orders
WHERE DATEPART(WEEKDAY, OrderDate) IN (1, 7); -- Sunday=1, Saturday=7

-- Orders on specific weekday (e.g., Monday)
SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM Orders
WHERE DATEPART(WEEKDAY, OrderDate) = 2; -- Monday

8. Performance Optimization for Date Filters

SARGable vs Non-SARGable Queries

sql

-- Non-SARGable (avoids index usage)
SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM Orders
WHERE YEAR(OrderDate) = 2023 AND MONTH(OrderDate) = 10;

-- SARGable (uses index on OrderDate)
SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM Orders
WHERE OrderDate >= '2023-10-01' AND OrderDate < '2023-11-01';

-- Non-SARGable (function on column)
SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM Orders
WHERE CAST(OrderDate AS DATE) = '2023-10-15';

-- SARGable (range query)
SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM Orders
WHERE OrderDate >= '2023-10-15' AND OrderDate < '2023-10-16';

Using Computed Columns for Frequent Filters

sql

-- Add computed column for date-only operations
ALTER TABLE Orders ADD OrderDateOnly AS CAST(OrderDate AS DATE);

-- Create index on computed column
CREATE INDEX IX_Orders_OrderDateOnly ON Orders(OrderDateOnly);

-- Now queries can use the index
SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM Orders
WHERE OrderDateOnly = '2023-10-15';

9. Handling Time Zones and UTC Dates

sql

-- Store dates in UTC
ALTER TABLE Orders ADD OrderDateUTC DATETIME2;

-- Convert local time to UTC for queries
SELECT OrderID, CustomerID, OrderDateUTC, TotalAmount
FROM Orders
WHERE OrderDateUTC >= DATEADD(HOUR, -5, GETUTCDATE()) -- EST to UTC conversion
  AND OrderDateUTC < GETUTCDATE();

-- Using AT TIME ZONE (SQL Server 2016+)
SELECT OrderID, 
       OrderDateUTC AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS OrderDateEST
FROM Orders
WHERE OrderDateUTC >= DATEADD(HOUR, -24, GETUTCDATE());

10. Common Pitfalls and Solutions

Pitfall 1: Time Component Issues

sql

-- Missing records due to time components
SELECT * FROM Orders 
WHERE OrderDate BETWEEN '2023-10-01' AND '2023-10-31'; -- Might miss records from Oct 31

-- Solution: Use exclusive upper bound
SELECT * FROM Orders 
WHERE OrderDate >= '2023-10-01' 
  AND OrderDate < '2023-11-01';

Pitfall 2: Performance with Functions

sql

-- Slow: Function on indexed column
SELECT * FROM Orders WHERE DATEDIFF(DAY, OrderDate, GETDATE()) < 7;

-- Fast: SARGable alternative
SELECT * FROM Orders WHERE OrderDate > DATEADD(DAY, -7, GETDATE());

Pitfall 3: Cultural Date Formats

sql

-- Avoid cultural-specific formats
DECLARE @DateVar DATETIME = '10/11/2023'; -- Is this Oct 11 or Nov 10?

-- Use unambiguous formats
DECLARE @SafeDate DATETIME = '20231011'; -- ISO format (YYYYMMDD)
DECLARE @SafeDate2 DATETIME = '2023-10-11T14:30:00'; -- ISO with time

11. Best Practices for Date Filtering

1. Use SARGable queries for better performance

2. Always use unambiguous date formats (YYYYMMDD or YYYY-MM-DD)

3. Consider time components in range queries

4. Use appropriate data types (DATE vs DATETIME2)

5. Create indexes on frequently filtered date columns

6. Use computed columns for complex date calculations

7. Consider time zones for global applications

8. Test with edge cases (leap years, month ends, etc.)

12. Complete Examples Package

sql

-- Example 1: Dashboard query for last 7 days
SELECT 
    CAST(OrderDate AS DATE) AS OrderDay,
    COUNT(*) AS TotalOrders,
    SUM(TotalAmount) AS DailyRevenue
FROM Orders
WHERE OrderDate >= DATEADD(DAY, -6, CAST(GETDATE() AS DATE))
  AND OrderDate < DATEADD(DAY, 1, CAST(GETDATE() AS DATE))
GROUP BY CAST(OrderDate AS DATE)
ORDER BY OrderDay;

-- Example 2: Monthly sales report
SELECT 
    YEAR(OrderDate) AS OrderYear,
    MONTH(OrderDate) AS OrderMonth,
    COUNT(*) AS TotalOrders,
    SUM(TotalAmount) AS MonthlyRevenue
FROM Orders
WHERE OrderDate >= DATEFROMPARTS(YEAR(GETDATE()), 1, 1) -- Current year
  AND OrderDate < DATEADD(YEAR, 1, DATEFROMPARTS(YEAR(GETDATE()), 1, 1))
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
ORDER BY OrderYear, OrderMonth;

-- Example 3: Time-based analysis
SELECT 
    DATEPART(HOUR, OrderDate) AS OrderHour,
    COUNT(*) AS OrdersPerHour,
    AVG(TotalAmount) AS AverageOrderValue
FROM Orders
WHERE OrderDate >= DATEADD(DAY, -30, GETDATE())
GROUP BY DATEPART(HOUR, OrderDate)
ORDER BY OrderHour;

Conclusion

Effective date filtering in SQL Server requires understanding of:

1. Date/Time data types and their appropriate usage

2. SARGable query writing for optimal performance

3. Range-based filtering instead of function-based filtering

4. Time component handling in date comparisons

5. Indexing strategies for date columns

Key recommendations:

• Use >= and < for date ranges instead of BETWEEN

• Avoid functions on indexed date columns in WHERE clauses

• Consider computed columns for frequent date operations

• Always test with time components and edge cases

• Use unambiguous date formats in your code

📘Free Job Preparation Zone 🎯 Visit Free Learning Zone