It would be much better if you used a proper type for dates, i.e.
DATE and not
VARCHAR, as that would give you several advantages:
- integrity: prevents nonsense data to be inserted in the column, like `20181301', '20190133', '2018ABCd', 'today', 'Xmas19', etc.
- storage and index size:
DATE is 3 bytes vs 9 of
- usability: let you use the ready made conversions and format functions (not for the specific query but possibly for other ones).
If you can't change the type, it's not so bad though as your format is at least an order preserving one, so you can use conditions that can utilize indexes:
WHERE PostingDate >= '20180101'
AND PostingDate < '20190101'
which by the way can still be used when (and if) you convert the column type to
DATE. Nothing would need to change.