CODEWITHSUNDEEP
sql-serverdatetime
Leonardo
Leonardo

Reputation: 81

Comparing two DATETIME columns in SQL

I have two datetime columns:

StartDate : 2019-07-01 13:30:00.000
AdmitDate : 2019-07-01 00:00:00.000

I want to select all rows that have the same date (excluding the time, seconds, etc). 

SELECT * 
FROM table1 as t1
    INNER JOIN table2 as t2 ON t1.id = t2.id
WHERE t1.startdate >= t2.admitdate or t1.startdate <= t2.admitdate

However, this won't work because one of the columns has hours, minutes and seconds. How to fix this? Again, I just want the date, not time.

Upvotes: 0

Views: 3154

Answers (2)

Barney CBZ
Barney CBZ

Reputation: 121

The solution is simpler to convert the columns in Date

it depends if both columns are datetime or one is date

SELECT Table1.Id,Table1.Startdate
FROM Table1
INNER JOIN Table2 ON Table1.Id = Table2.Id
where (cast(Table1.Startdate as date))=(cast(Table2.AdmitDateTime as date))


SELECT Table1.Id,Table1.Startdate 
FROM Table1
INNER JOIN Table2 ON Table1.Id = Table2.Id
where (cast(Table1.Startdate as date))=Table2.AdmitDate

Example

Upvotes: 1

Isaac
Isaac

Reputation: 3363

How about using DATEDIFF()?

SELECT * 
FROM table1 as t1
INNER JOIN table2 as t2 ON t1.id = t2.id
WHERE DATEDIFF(DAY, t1.startdate, t2.admitdate) = 0

Upvotes: 1

Related Questions