CODEWITHSUNDEEP
phpmysqlsql
user5514909
user5514909

Reputation: 21

Inconsistencies with MysQL DATEDIFF Function

Ok, so I have the following MySQL query:

SELECT COUNT(*) FROM `dates` WHERE firstDate IS NOT NULL AND secondDate IS NOT NULL  
AND DATEDIFF(secondDate, firstDate) >= 30

That query returns the value of 9. I then use the following query:

SELECT COUNT(*) FROM `dates` WHERE firstDate IS NOT NULL AND secondDate IS NOT NULL  
AND DATEDIFF(secondDate, firstDate) >= 90

The second query returns a result of 12. How is that possible? Both dates are date data types in the MySQL Database.

Upvotes: 1

Views: 51

Answers (1)

George Jonsson
George Jonsson

Reputation: 41

Try doing this

SELECT firstDate, secondDate, DATEDIFF(secondDate, firstDate) AS diff 
FROM `dates` WHERE firstDate IS NOT NULL AND secondDate IS NOT NULL
AND DATEDIFF(secondDate, firstDate) >= 30

SELECT firstDate, secondDate, DATEDIFF(secondDate, firstDate) AS diff
FROM `dates` WHERE firstDate IS NOT NULL AND secondDate IS NOT NULL
AND DATEDIFF(secondDate, firstDate) >= 90

Instead of the count of the resulting rows you will see the actual list of rows. That might help you to figure this one out.

Upvotes: 2

Related Questions