MSSQL varchar和PHP字符串之间的日期比较不起作用 - 为什么？

Question

So I got a database running on a Microsoft SQL Server 11, with the table hpfc_prices containing multiple entries in a chronologic sequence, which roughly looks like this:

|date (varchar(255))|time (varchar(255))|price (decimal(25,10))|
|===================|===================|======================|
|01.01.2016         |00:00              |[some value]          |
|01.01.2016         |01:00              |[some value]          |
|01.01.2016         |02:00              |[some value]          |
|...                |...                |...                   |
|01.01.2016         |23:00              |[some value]          |
|02.01.2016         |00:00              |[some value]          |
|02.01.2016         |01:00              |[some value]          |
|...                |...                |...                   |

This table is spanning from January 1st 2016 to December 31st 2020, each entry representing one hour. Now I want to load parts of the table, let's say the whole year 2017, in a PHP script. For that I have to use Medoo as database framework, and I'm running following code:

$db = new medoo($database_config);
$hpfcStart = "01.01.2017";
$hpfcEnd = "31.12.2017";
$arr = $db->select("hpfc_prices",["date","time","price"],["AND" => ["date[>=]" => $hpfcStart, "date[<=]" => $hpfcEnd]]);
var_dump($arr);

The Medoo query is by debug translated to this SQL query:

SELECT "date","time","price" FROM "hpfc_prices" WHERE "date" >= '01.01.2017' AND "date" <= '31.01.2018

The problem now is that the output now contains all of the table, and not just specifically all entries for 2017. I assume the query fails at the date comparison, since Medoo compares a string with a varchar. In my experience this worked with MySQL, but I'm not so sure about MSSQL/Medoo. Can anyone tell me how I can compare the dates correctly?

Answer 1

If you are willing to use Medoo's query() function alongside quote() then try this:

WHERE convert(date, 104) >= convert('01.01.2017', 104)

For the record, this is going to kill performance. Not sure of MSSQL's index capabilities but I would add an index with this definition if possible

convert(date, 104)