Why didn't they see all the data? Because they did a search by date and some of the dates were stored via the website in the form of '2008-01-01 00:00:00' and some were stored via the database in the form of '2008-01-01 12:35:49'.
Now, for some magical reason, if you hide the time in the date in your searches, like so:
where signupdate between '2008-01-01' and '2008-01-02'then you might not see all the data between the days 01 to 02.
where signupdate between date('2008-01-01 00:00:00') and date('2008-01-02 00:00:00')
Depending on which way your date was stored, you might only see the dates between those 2 dates and not equal to those dates as well.
So for example, if you had:
and you ran the search above, then you would see:
(I once worked for another company that booked hotel rooms online. For them when you booked the room, the nights were counted. So if you would do a search, on the website, all the date allocations would be set to midnight - 00:00:00.)
In our situation we had a mix of dates which caused use problems with 1 table and was ok with another.
We decided to solve the problem like this
where signupdate between '2008-01-01 00:00:00' and ' 2008-01-02 23:59:59'
That is, from what I understand, the time a day starts (00:00:00) and the time a day ends (23:59:59). So we figured we covered all the issues with this statement and we are happy with it.
I hope it helps you to if you also run into these kind of problems.