SQL
Query Data by Date Range and Extract Date Components in SQL
Learn essential SQL functions to filter database records based on specific date and time ranges and how to extract granular components like year, month, or day.
-- Select all records for a specific date range
SELECT
*
FROM
orders
WHERE
order_date >= '2023-01-01' AND order_date < '2023-02-01';
-- Alternative for BETWEEN (inclusive)
-- SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';
-- Extract year and month from a date column (PostgreSQL/SQL Server/MySQL examples)
SELECT
order_id,
order_date,
EXTRACT(YEAR FROM order_date) AS order_year_pg,
EXTRACT(MONTH FROM order_date) AS order_month_pg,
DATEPART(YEAR, order_date) AS order_year_sqlserver,
DATEPART(MONTH, order_date) AS order_month_sqlserver,
YEAR(order_date) AS order_year_mysql,
MONTH(order_date) AS order_month_mysql
FROM
orders
WHERE
order_date IS NOT NULL;
-- Filter records for current month/year (example for PostgreSQL)
SELECT
*
FROM
orders
WHERE
EXTRACT(YEAR FROM order_date) = EXTRACT(YEAR FROM CURRENT_DATE) AND
EXTRACT(MONTH FROM order_date) = EXTRACT(MONTH FROM CURRENT_DATE);
How it works: This snippet covers fundamental SQL date and time manipulations. It first demonstrates how to filter records within a specific date range using comparison operators or the `BETWEEN` keyword. It then illustrates how to extract individual components like year, month, or day from a datetime column using functions like `EXTRACT` (PostgreSQL), `DATEPART` (SQL Server), or `YEAR()`/`MONTH()` (MySQL), providing examples for common database systems. Finally, it shows how to filter data for the current month and year dynamically.