I have a date field that is in a 'yyyymmdd' format, and I need to filter the query by the year, using that field.
For example, in my table there is a "Posting Date" field that is in the '20180401' format, and I need to filter the query to only the records with a year of 2018.
Best Answer
It would be much better if you used a proper type for dates, i.e.
DATE
and notVARCHAR
, as that would give you several advantages:DATE
is 3 bytes vs 9 ofVARCHAR(8)
.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:
which by the way can still be used when (and if) you convert the column type to
DATE
. Nothing would need to change.