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.

  • Is this "date" field declared as a char or int type? Or is it really a date and the format that you're seeing is what's being converted by your client application? – alroc Jun 12 '19 at 0:32
  • The latter, where it's really a date that's already been converted by the client application. – Mike Jones Jun 12 '19 at 17:24
  • If it's being stored as a date type, then where PostingDate >= '2018-01-01' and PostingDate < ' 2019-01-01' should suffice. – alroc Jun 12 '19 at 17:30

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 VARCHAR(8).
  • 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.

| improve this answer | |
  • That would technically solve for it, but I was hoping to simplify it some by saying "WHERE PostingDate = 2018 – Mike Jones Jun 11 '19 at 18:07
  • 2
    You could write it differently but that would make the query slower. – ypercubeᵀᴹ Jun 11 '19 at 18:07
  • If it's varchar then can't you use: WHERE PostingDate LIKE '2018%' ? – Queue Mann Jun 11 '19 at 18:24
  • Yes, you could use that, too, for varchar. I don't like it but yes, you could. – ypercubeᵀᴹ Jun 11 '19 at 18:28
  • 2
    ...of course that will break when you fix the schema. Never store dates as strings for the same reason you don't put pans in the silverwear drawer or hang underwear in the hall closet. – Aaron Bertrand Jun 11 '19 at 18:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.