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.