3 ms·

You don't have to put a full timedate inside the comparison string. Writing `BETWEEN '2018-01-01' AND '2019-01-01'` works correctly and it's pretty readable. H

You don't have to put a full timedate inside the comparison string. Writing `BETWEEN '2018-01-01' AND '2019-01-01'` works correctly and it's pretty readable. However, I agree that making `BETWEEN` use closed intervals is one of the many design-level mistakes on SQL.

quietbritishjim 7y ago
`BETWEEN '2018-01-01' AND '2019-01-01'` does not work correctly. It will match '2019-01-01 00:00:00.000'. Assuming that this column only contains dates (i.e. with the time always set to 00:00:00.000) this means that 2019-01-01 will always be matched, one whole extra day than is required. `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.
- mFixman 7y ago
  If the dates are stored in strings, then you are wrong in both counts. '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.
  - quietbritishjim 7y ago
    > If the dates are stored in strings 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'.

quietbritishjim 7y ago

`BETWEEN '2018-01-01' AND '2019-01-01'` does not work correctly. It will match '2019-01-01 00:00:00.000'. Assuming that this column only contains dates (i.e. with the time always set to 00:00:00.000) this means that 2019-01-01 will always be matched, one whole extra day than is required. `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.

mFixman 7y ago
If the dates are stored in strings, then you are wrong in both counts. '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.
- quietbritishjim 7y ago
  > If the dates are stored in strings 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'.