CODEWITHSUNDEEP
mysqlsqldatetime
bizard
bizard

Reputation: 176

compare datetime with date and time

I have currently two table like this :
enter image description here
And I want to compare my Datetime(table2) with my column date and time with only one condition but this doesn't work : 

SELECT id
FROM Table1
WHERE Date+Time>(SELECT Datetime FROM Table2 WHERE Id=1)
AND Id=1

Upvotes: 1

Views: 278

Answers (2)

Michael
Michael

Reputation: 673

SELECT Id
FROM Table1
WHERE CAST(Date as DATETIME) + CAST(Time as TIME)>= (SELECT Datetime FROM Table2 WHERE Id=1)
AND Id=1

But note you won´t get a result as far as your date is equal and you use ">" for your query.

Upvotes: 1

MontyPython
MontyPython

Reputation: 3012

One of the following two should work out just fine.

select id from table1
where concat(date,' ',time) > (select datetime from table2 where id = 1)
and id = 1;

or

select t1.id from table1 t1 inner join table2 t2
on t1.id = t2.id and concat(t1.date,' ',t1.time) > t2.datetime;

There is a possibility that you might need explicit conversion of time and date columns to char. I doubt it, though.

Upvotes: 2

Related Questions