Fetching rows from the previous month is a common requirement in reporting, analytics, and automated email summaries. Getting this right requires understanding of date functions, SQL syntax, and edge cases like year changes.

Objective

Retrieve all rows from a table where a DATE, DATETIME, or TIMESTAMP column falls within the entire previous calendar month, no matter the current date.

Example Table: orders

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(255),
    total DECIMAL(10, 2),
    order_date DATE
);

Step-by-Step SQL Query (Using MySQL)

✅ Step 1: Use CURDATE() to Get Today's Date

SELECT CURDATE(); -- Returns current date, e.g., '2025-05-04'

✅ Step 2: Get First Day of the Previous Month

SELECT DATE_SUB(DATE_SUB(CURDATE(), INTERVAL DAY(CURDATE()) - 1 DAY), INTERVAL 1 MONTH);
-- Example: Returns '2025-04-01'

✅ Step 3: Get First Day of the Current Month

SELECT DATE_SUB(CURDATE(), INTERVAL DAY(CURDATE()) - 1 DAY);
-- Example: Returns '2025-05-01'

✅ Step 4: Final Query to Get All Rows from the Previous Month

SELECT * 
FROM orders
WHERE order_date >= DATE_SUB(DATE_SUB(CURDATE(), INTERVAL DAY(CURDATE()) - 1 DAY), INTERVAL 1 MONTH)
  AND order_date < DATE_SUB(CURDATE(), INTERVAL DAY(CURDATE()) - 1 DAY);

This retrieves all orders from April 1 to April 30, if today is May 4.

⌛ Using LAST_DAY() Alternative

SELECT * 
FROM orders
WHERE order_date BETWEEN 
      DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01') AND 
      LAST_DAY(NOW() - INTERVAL 1 MONTH);

• DATE_FORMAT(..., '%Y-%m-01'): Gets the first day of the previous month.

• LAST_DAY(...): Gets the last day of the previous month.

✅ Full Code Example

Here’s a full working SQL snippet:

-- Create table
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(255),
    total DECIMAL(10,2),
    order_date DATE
);

-- Insert sample data
INSERT INTO orders (customer_name, total, order_date) VALUES
('Alice', 150.00, '2025-04-05'),
('Bob', 200.00, '2025-04-20'),
('Charlie', 75.00, '2025-05-02');

-- Query all orders from the previous month
SELECT * 
FROM orders
WHERE order_date BETWEEN 
      DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01') AND 
      LAST_DAY(NOW() - INTERVAL 1 MONTH);

✅ Expected Output (if today is May 4): Only orders from April will be retrieved.

Tips

1. Use BETWEEN for Inclusive Range: It includes both start and end dates.

2. Always Check Data Types: Your date column should be of DATE, DATETIME, or TIMESTAMP type.

3. Be Timezone-Aware: If using TIMESTAMP, consider the server timezone vs user timezone.

4. Use NOW() or CURDATE() Consistently: NOW() gives current date and time, CURDATE() gives just the date.

⚠️ Common Pitfalls

• ❌ Incorrect Use of MONTH()/YEAR() Only:

-- Avoid this if you care about exact boundaries
WHERE MONTH(order_date) = MONTH(CURDATE()) - 1

This breaks in January because it doesn't handle year transitions.

• ❌ Forgetting to Normalize Dates:

  • Using NOW() instead of stripping time portion can cause partial data to be skipped.

• ❌ Not Indexing Date Column:

  • Large datasets should index order_date for performance.

Bonus: Works Across Different SQL Dialects

• ✅ PostgreSQL:

SELECT * FROM orders
WHERE order_date >= date_trunc('month', CURRENT_DATE - INTERVAL '1 month')
  AND order_date < date_trunc('month', CURRENT_DATE);

• ✅ SQL Server:

SELECT * FROM orders
WHERE order_date >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)
  AND order_date < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0);

Conclusion

Querying rows from the previous calendar month requires careful date calculation. Avoid shortcuts like subtracting 30 days, and instead rely on first day/last day logic. This makes your query resilient to month-length variations and year changes.