Searching Problems with Dates and Midnight

Today, we run into an issue when a client of ours. They wanted to see some of the data on our website and when doing a search, they didnt see all the days they asked for in the search

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'
or
where signupdate between date('2008-01-01 00:00:00') and date('2008-01-02 00:00:00')

then you might not see all the data between the days 01 to 02.
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:

2008-01-01 00:00:00
2008-01-01 00:00:00
2008-01-02 00:00:00
2008-01-02 00:00:00
2008-01-03 00:00:00
2008-01-03 00:00:00

and you ran the search above, then you would see:

2008-01-01 00:00:00
2008-01-01 00:00:00

(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.