SQL query to select dates between two dates

Selecting dates between two dates in SQL is a very common requirement, especially when working with reports, logs, or time-based data. SQL provides the BETWEEN operator and comparison operators (>=, <=) to make this task simple and efficient.

For example, if you have a table called orders with a column order_date, and you want to select all records between January 1, 2023, and March 31, 2023, you can write:

SELECT *  

FROM orders  

WHERE order_date BETWEEN '2023-01-01' AND '2023-03-31';

• This will include both the start and end dates in the result set.
  
• Alternatively, you can use greater-than and less-than conditions for more control:
  

SELECT *  

FROM orders  

WHERE order_date >= '2023-01-01'  

  AND order_date <= '2023-03-31';

This approach is useful if you want to be explicit or handle edge cases (like excluding one of the boundaries).

Key points to remember:

• BETWEEN is inclusive → It includes both start and end dates.
  
• Date format matters → Always ensure your date format matches the database system (e.g., YYYY-MM-DD).
  
• Time component in DATETIME → If the column has a time value, you may need to adjust the end date (e.g., 2023-03-31 23:59:59).
  
• Works across databases → Most SQL engines (MySQL, SQL Server, Oracle, PostgreSQL) support these approaches with minor syntax variations.