Clever Geek Handbook

SQL: how to compare dates while ignoring time?

How can I compare only the dates of timestamps, ignoring the time?

I just want to compare month / date / year. For example:

select * from Batch
where openingtime <> closingtime

The problem is that this will display too many batches, as it will include batches where OpeningTime

and ClosingTime

differ only in time of day:

OpeningTime = 2010-07-07 11:19:29.000

ClosingTime = 2010-07-07 19:19:22.000

+3


source to share


3 answers


discard both timestamps as dates

For SQL Server

Select * 
from Batch 
where cast(openingtime as date) <> cast(closingtime as date)


For Oracle

Select * 
from Batch 
where trunc(openingtime) <> trunc(closingtime)
+2


source


Another way



Select * from Batch where      
CONVERT(VARCHAR(10),openingtime,110<>CONVERT(VARCHAR(10),closingtime,110)
+2


source 


Select * from Batch where      
CONVERT(VARCHAR(10),openingtime,110)<>CONVERT(VARCHAR(10),closingtime,110)

**There will be a closing bracket** @Miyamoto Musashi
+2


source






More articles:


All Articles