Date is hard. The example says
> BETWEEN '2018-01-01 00:00:00:000' and '2018-12-31 00:00:00:000'
So the last day of december will be missing.
> BETWEEN '2018-01-01 00:00:00:000' and '2018-12-31 00:00:00:000'
So the last day of december will be missing.
It would be safer, and clearer, by doing the half-open comparison explicitly. But I would argue it's BETWEEN that's hard (because it's closed rather than half-open as you'd expect) rather than dates, at least in this case.
orderdate >= '2018-01-01 00:00:00:000' AND orderdate < '2019-01-01 00:00:00:000'
However, I agree that making `BETWEEN` use closed intervals is one of the many design-level mistakes on SQL.
`BETWEEN '2018-01-01' AND '2018-12-31'`, which as you say is just shorthand for what the article says, will correctly match '2018-12-31' (again, assuming the time in all values is precisely 00:00:00.000) and will not '2019-01-01', which is the correct behaviour.
'2019-01-01' < '2019-01-01 00:00:00.0000', so that time won't match the date. '2018-12-31' < '2018-12-31 00:00:00.000', to it will not match that either.
The rest of your comment is fairly academic because, surely, the dates are stored as a native date/time type. (Either `date` itself or `timestamp without timezone` - probably the latter given that the literals in the article mention times.) Using strings would both be inefficient cause numerous correctness problems, including, as you just said, the fact that comparison with '2018-12-31' would give different results to comparison with '2018-12-31 00:00:00.000'. There's no reason to assume that the article had chosen this insane path.
Just to humour the possibility that someone had, foolishly, used a string for this possibility: Your most recent comment is correct that as strings '2018-12-31' < '2018-12-31 00:00:00.000', so the BETWEEN in my previous comment would not have worked (although it depends, of course, on what exact strings were stored in the column!). But the comparison you suggested in your previous comment would also be incorrect, for the same reason I already objected to: `BETWEEN '2018-01-01' AND '2019-01-01'` will match (the string) '2019-01-01'.