mysqlsqldatecharsql-convert

SQL - char field with date - how to search between two dates

I have simple problem with SQL query.. I would like to search fields, where dates begin and ends in specific date and time.

Field is defined as CHAR and have structure: DD.MM.YYYY hh:mm:ss and I cannot change it.

I tried to declare some variables and search by this, also tried by Converting.

This is what I tried and didn't work: 

SELECT date FROM table WHERE date BETWEEN '1.01.2017 00:00:00' AND '1.02.2017 23:59:59'

SELECT date FROM table WHERE date >= '1.01.2017 00:00:00' AND date <= '1.02.2017 00:00:00'

SELECT date FROM table WHERE date >= Convert(DATETIME, '1.01.2017', 104) AND date <= Convert(DATETIME, '1.02.2017', 104)

Always after this query I get all dates, not this what I asked.

Solution

  • I solved my problem. Problem was with that HOW this fields are storage in DB. I thought that they are storage like: DD.MM.YYYY hh:mm:ss, but it was only structure. In DB they are storage like: YYYYMMDDhhmmss and after changes in WHERE query line it works.

    SELECT date FROM table WHERE date >= '20170101000000' AND date <= '20170101235959'