this is happening because the values stored in the column are DATETIMEs, and all values for the last day have a time component, but the upper bound for your range is midnight on the morning of the last day
your example is --
this is equivalent to --
WHERE recordtime BETWEEN '2009-01-01' AND '2009-01-12'
and of course then any time on the 12th will not be included
WHERE recordtime BETWEEN '2009-01-01 00:00:00' AND '2009-01-12 00:00:00'
you ~could~ change it to this --
and this will correctly include all times on the 12th, but it will also incorrectly include '2009-01-13 00:00:00'
WHERE recordtime BETWEEN '2009-01-01' AND '2009-01-13'
another way some people deal with this is ----
but this is also incorrect, or at least has the potential to be incorrect, if you do not write the upper end value accurately enough down to the millisecond or whatever (and if the underlying technology changes, to specify time down to microseconds, you have to re-write that upper limit)
WHERE recordtime BETWEEN '2009-01-01 00:00:00' AND '2009-01-12 23:59:59'
the best way to handle this is to use a range that is open-ended at the upper end --
can you see how that would always work, no matter how fine a time division is implemented?
WHERE recordtime >= '2009-01-01'
AND recordtime < '2009-01-13'
furhermore, it also works really nicely, without alteration, if you're storing DATEs instead of DATETIMEs
in other words, the upper open end approach works correctly in all circumstances