3

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

Michael Currie
  • 13,721
  • 9
  • 42
  • 58
Brian
  • 51
  • 5
  • possible duplicate of [Compare dates in T-SQL, ignoring the time part](http://stackoverflow.com/questions/1427469/compare-dates-in-t-sql-ignoring-the-time-part) – Michael Currie Jul 13 '15 at 22:31
  • Which DBMS are you using? Postgres? Oracle? –  Jul 14 '15 at 06:52

3 Answers3

2

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)
lookslikeanevo
  • 566
  • 1
  • 5
  • 14
  • what code can I add to so closingtime had to be after 3:00AM? – Brian Jul 13 '15 at 22:46
  • by after 3am would that mean that you want to count stuff milliseconds after 3am? if so then in the where clause add: and datepart(hour, closingtime) >= 3 – lookslikeanevo Jul 15 '15 at 04:34
2

Another way

Select * from Batch where      
CONVERT(VARCHAR(10),openingtime,110<>CONVERT(VARCHAR(10),closingtime,110)
Miyamoto Musashi
  • 53
  • 7
2
Select * from Batch where      
CONVERT(VARCHAR(10),openingtime,110)<>CONVERT(VARCHAR(10),closingtime,110)

**There will be a closing bracket** @Miyamoto Musashi
Keulraesik
  • 59
  • 5