CODEWITHSUNDEEP
mysqlmariadb
Devidas M Das
Devidas M Das

Reputation: 478

Unable to find hours between two dates MySQL

I have a table in my database and I have two date columns (varchar) in my table date1 and date2. I want to find difference in hours between date1 and date2. Database is created using mariadb.

I already tried:

timediff('date1','date2')

But getting null.

Upvotes: 1

Views: 189

Answers (3)

Rick James
Rick James

Reputation: 142306

TIMESTAMPDIFF(HOUR, start_date, end_date)

Example:

mysql> SELECT NOW(), TIMESTAMPDIFF(HOUR, '2015-12-09 08:00:00', NOW());
+---------------------+---------------------------------------------------+
| NOW()               | TIMESTAMPDIFF(HOUR, '2015-12-09 08:00:00', NOW()) |
+---------------------+---------------------------------------------------+
| 2015-12-09 14:12:51 |                                                 6 |
+---------------------+---------------------------------------------------+

Upvotes: 1

Naruto
Naruto

Reputation: 4329

THe issue is you are passing column name as String so it is unable to parse it. Remove quotes from the column and it will run as a charm.

SELECT timediff(date1, date2) from TABLE

EDIT:- Use STR_TO_DATE() method to convert string to date type... So your final Query Will be 

SELECT timediff(STR_TO_DATE(date1, '%m-%d-%Y %H:%i:%s'),STR_TO_DATE(date2, '%m-%d-%Y %H:%i:%s')) from TABLE

Upvotes: 1

Gouda Elalfy
Gouda Elalfy

Reputation: 7023

You can use STR_TO_DATE to convert varchar to date, so your query should be:

timediff(STR_TO_DATE(date1, '%m/%/d/%Y %h:%i:%s %p'), (STR_TO_DATE(date2, '%m/%/d/%Y %h:%i:%s %p'))

Upvotes: 1

Related Questions