CODEWITHSUNDEEP
sql
Brian
Brian

Reputation: 51

SQL: How to compare dates while ignoring the time?

How can I compare just the dates of timestamps while ignoring the times?

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

select * from Batch
where openingtime <> closingtime

The problem is that this will show too many batches, since it will include batches where OpeningTime and ClosingTime differ only in the time of day:

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

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

Upvotes: 3

Views: 85

Answers (3)

Keulraesik
Keulraesik

Reputation: 59

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

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

Upvotes: 2

lookslikeanevo
lookslikeanevo

Reputation: 565

cast 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)

Upvotes: 2

Miyamoto Musashi
Miyamoto Musashi

Reputation: 53

Another way

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

Upvotes: 2

Related Questions