If possible, do not to use a data type with a higher precision than "one day" if you do not need a better precision. If your database, or your programming language only provides you with a data type for date & time combined, then set the time part to zero when mapping it to "date only", anything else will make things obfuscated for the maintenance programmer which comes after you.
That will still leave open if one should store
for the end date of an interval.
Date arithmetics can be implemented correctly with both approaches, there are pros and cons for both sides (this is quite similar to some programming languages defining array bounds for
a[n] from 0 to n-1, whilst other languages provide "1 to n" semantics). The obvious "pro" argument for the first is, the semantics is more alike to what most people expect, as you already wrote, so it is probably less error prone in its interpretation.
The argument for the second approach is, to calculate the number of days in the interval between start and end date, one can just build the "difference" between both dates, without the need for adding 1 afterwards. Same holds when one gets a point
p in time where
p is a "date&time" variable, and the task is to check if
p is in the range from
end_date, one will also save an addition of one day here (no less, no more).
However, both representions have a certain risk of introducing off-by-one errors, that is quite inevitable. So there is IMHO no "better" or "optimal" solution, choose the one which serves you best, which fits better to your overall requirements, or to the existing system. If you still can't make a decision, throw a dice. However, if you have more than one place in your application dealing with date intervals, I would recommend to be consistent in how you implement it.