I am trying to apply a filter on dates which is a string type but I am not able to get the desired results.

for eg, select * from table_1 where date between '01-01-2016' and '31-12-2016';

The output to this query also includes results from 2015 and 2013. Why is this happening?

closed as unclear what you're asking by Marcello Miorelli, Marco, John Eisbrener, mustaccio, LowlyDBA Jul 2 at 0:08

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    I am vote to close this question because I believe it is not clear, as you don't provide enough information about your environment in order for people to be able to help you. Please edit your question, adding the relevant information. – Marcello Miorelli Jun 30 at 19:17
  • Please provide a Minimal, Complete, and Verifiable Example, as this will provide proper scope around what your actual problem is. – John Eisbrener Jul 1 at 13:31

This happens because string comparison knows nothing about dates and how they should be compared. Strings are always compared char by char and 31-12-2016 is bigger than 30-12-2019 or 25-12-0000.

You have to use native DATE or DATETIME types that are timeline and timezone aware and has a number of useful bundled functions.

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