SQL Between Two Dates (Best 7 Examples with Dates, Numbers in MS SQL Server and MySQL)

sql between two dates (Best 7 Examples with Dates, Numbers in MS SQL Server and MySQL) with Fetching records between two date ranges with syntax and examples.

how to use between in sql? : We have a start_date and end_date. We want to get the list of dates in between these two dates.

sql between two dates

SQL Between Syntax

expression BETWEEN value1 AND value2;

OR

SELECT Column(s) FROM table_name WHERE column BETWEEN value1 AND value2;

The BETWEEN operator used : between operator in sql

• DELETE statements
• UPDATE statements
• INSERT statements
• SELECT statements

for specifying the range.

BETWEEN 10 AND 20

This is equivalent to

>=10 AND <=20

difference in dates sql with PHP

$join_date = new DateTime(date('y-m-d'));
$last_res_dt = new DateTime($data['last_end_date']);
$interval = $join_date->diff($last_res_dt);
$final_date = $interval->format('%r%a');

The example of BETWEEN two dates

Query: sql between two dates

SELECT * FROM sto_members
 
WHERE join_date BETWEEN '01-01-2022' AND '12-12-2022';

Between two years

SELECT * FROM `tamilrokers` WHERE year( dt2 ) between 2020 and 2025

Between two month ranges

SELECT * FROM `tamilrokers` WHERE month(dt) BETWEEN '02' and '08'

SELECT * FROM `tamilrokers` WHERE MONTH(dt) 
	BETWEEN '02' and '08' AND YEAR(dt) BETWEEN 2020 and 2025

SELECT FROM table_name WHERE dte_field 
	BETWEEN '2022-03-01' AND LAST_DAY('2022-02-01')

Between two date ranges

sql between two dates

SELECT * FROM `tamilrokers` WHERE dt BETWEEN '2025-01-01' AND '2025-12-31' 

Date Format to use in query

$date = new DateTime($dt2);
$dt2=$date->format('Y-m-d');

SELECT * FROM `tamilrokers` WHERE dt BETWEEN BETWEEN '$dt1' AND '$dt2' 

Using NOT with BETWEEN operator

SELECT * FROM sto_members
 
WHERE join_date NOT BETWEEN '01-01-2022' AND '12-12-2022';

Using numeric values in BETWEEN operator

SELECT * FROM sto_members
 
WHERE id BETWEEN 2 AND 5;

Using multiple BETWEEN with OR operator

SELECT * FROM sto_members
 
WHERE id BETWEEN 1 AND 3
 
OR id BETWEEN 5 AND 7;

Using text values in BETWEEN operator

SELECT * FROM sto_members
 
WHERE member_name BETWEEN 'Jimmy' AND 'Mike';

Using the DISTINCT clause with BETWEEN operator

SELECT DISTINCT member_name FROM sto_member_salary_paid
 
WHERE member_id BETWEEN 2 AND 5;

BETWEEN with GROUP BY and HAVING clause

SELECT member_name, SUM(member_sal_paid) As "Paid"
 
FROM store_db.dbo.sto_member_salary_paid
 
GROUP BY member_name
 
HAVING SUM(member_sal_paid) BETWEEN 5000 AND 12000;

How to get all dates between date range in SQL query?

DECLARE @JoinOnTime DATE, @UpdatedOnTime DATE
SET @JoinOnTime = '20220528'
SET @UpdatedOnTime = '20220531'

SELECT DATEADD(DAY,number+1,@JoinOnTime) [Date]
FROM master..spt_values
WHERE type = 'P'
AND DATEADD(DAY,number+1,@JoinOnTime) < @UpdatedOnTime

List all Dates between two dates in SQL Server

DECLARE @JoinOnTime DATETIME
DECLARE @UpdatedOnTime DATETIME
 
SET @JoinOnTime = '2022-01-01'
SET @UpdatedOnTime = '2022-01-12';
 
WITH DateRange(Members) AS 
(
    SELECT @JoinOnTime as Date
    UNION ALL
    SELECT DATEADD(d,1,Members)
    FROM DateRange 
    WHERE Members < @UpdatedOnTime
)
SELECT Members
FROM DateRange
OPTION (MAXRECURSION 0)
GO

Result:

Members
-----------------------
2022-01-01 00:00:00.000
2022-01-02 00:00:00.000
2022-01-03 00:00:00.000
2022-01-04 00:00:00.000
2022-01-05 00:00:00.000
2022-01-06 00:00:00.000
2022-01-07 00:00:00.000
2022-01-08 00:00:00.000
2022-01-09 00:00:00.000
2022-01-10 00:00:00.000
2022-01-11 00:00:00.000
2022-01-12 00:00:00.000

MULTIPLE BETWEEN operators

Get the Products name with price between 20 and 35 and avarage between 90 to 98 percentage.

SELECT productPrice FROM Products WHERE 
ProductType BETWEEN 20 AND 35 
AND
productPercent BETWEEN 90 AND 98;

SQL BETWEEN operator for Date value

in sql between two dates: Get the price of products whose productions is between 1st Jan 2022 and 31st Jan 2022 using date between in sql.

SELECT ProductPrice FROM products WHERE productions BETWEEN str_to_date('2022-01-01', '%Y-%m-%d') AND '2022-01-31';

date between two dates sql

SQL query to select data between two dates using sql between two dates Example

SELECT * FROM  [table] 
WHERE [time] >='2022-04-08 23:53:00.000' AND [time] <= '2022-04-08 23:58:00.000'

Also Read : Advanced Mysql Sub Complex join Queries Tutorial