How to Compare Date in SQL Server Query?

Today, We want to share with you compare date in sql.In this post we will show you How to compare datetime with only date in SQL Server, hear for How to Compare Date in SQL Server Query? Finding All Rows Between Two Dates we will give you demo and example for implement.In this post, we will learn about Date Between In Sql with an example.

Query comparing dates in SQL

In this tutorial we learn to all about date between in sql Examples like as a SQL WHERE BETWEEN Clause, SQL Between, MySQL Between Dates, Not Between or many more.

Query with Comparing Dates in SQL

IF OBJECT_ID( 'tempdb..#Members' ) IS NOT NULL DROP TABLE #Members; 

CREATE TABLE #Members (member_name varchar(10), member_date datetime); 

INSERT INTO #Members VALUES ('Java', '2021-11-09 11:16:10.496'); 
INSERT INTO #Members VALUES ('MySQL', '2021-11-10 00:00:00.000'); 
INSERT INTO #Members VALUES ('SQL SERVER', '2021-11-10 11:26:10.193' ); 
INSERT INTO #Members VALUES ('PostgreSQL', '2021-11-10 12:36:10.393'); 
INSERT INTO #Members VALUES ('Oracle', '2021-11-11 00:00:00.000');

Now, you need to write a query to get all members which are on ‘2021-11-10’? Right query, should return 3 rows containing dates starting with ‘2021-1-07’, as shown below:

'MySQL', '2021-11-10 00:00:00.000' 
'SQL SERVER', '2021-11-10 11:26:10.193' 
'PostgreSQL', '2021-11-10 12:36:10.393'

SELECT * FROM #Members WHERE member_date = '2021-11-10' member_name member_date MySQL 2021-11-10 00:00:00.000

Comparing dates with between clause

SELECT * FROM #Members 
WHERE member_date between '2021-11-10' and '2021-11-11' member_name member_date 

MySQL 2021-11-10 00:00:00.000 
SQL SERVER 2021-11-10 11:26:10.193 
PostgreSQL 2021-11-10 12:36:10.393 
Oracle 2021-11-11 00:00:00.000

Always Use >= and < to compare dates

SELECT * FROM #Members WHERE member_date >= '2021-11-10' and member_date < '2021-11-11' member_name member_date 

MySQL 2021-11-10 00:00:00.000 
SQL SERVER 2021-11-10 11:26:10.193 
PostgreSQL 2021-11-10 12:36:10.393

Example 1: Range: date format maybe vendor dependent

SELECT memberName, memberDesc FROM members WHERE createdDate >= '2021-06-01' and createdDate < '2021-06-02' 

Example 2: SQL-92 Cast: data type maybe vendor dependent.

SELECT memberName, memberDesc FROM members WHERE CAST(createdDate AS DATE) = '2021-06-01'

Example 3: MS SQL Convert: vendor dependent but other databases

SELECT memberName, memberDesc FROM members WHERE Convert(DATE, createdDate) = '2021-06-01'

Example 4: MySQL Date: vendor dependent but other databases

SELECT memberName, memberDesc FROM members WHERE Date(createdDate) = '2021-06-01'

Example 5: Oracle/Postgres Trunc: vendor dependent

SELECT memberName, memberDesc FROM members WHERE Trunc(createdDate) = '2021-06-01'

I hope you get an idea about compare date in sql.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.