Date comparison between MSSQL varchar and PHP string does not work - why?

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?

Don't store dates as varchars. All your problems will go away. Or at least use a proper order from larger to smaller (year, month, day) — Sami Kuhmonen, Feb 22 '16 at 16:48
I'm aware that varchar is rather bad for storing dates, but I have no control over the database, so I have to find another way... — StrikeAgainst, Feb 22 '16 at 16:49
If you are stuck using these horrendous strings for "dates" you can't use comparison logic. Especially since it has day first. You are going to have to get these values as dates before you can do a realistic comparison. — Sean Lange, Feb 22 '16 at 16:51
@GordonLinoff It does when [QUOTED_IDENTIFIER](https://msdn.microsoft.com/en-us/library/ms174393.aspx) is set to ON. Which I am fairly sure is the default — GarethD, Feb 22 '16 at 16:53
@Gordon, it is, I just reconstructed the table by myself, as I have no access to it by now. — StrikeAgainst, Feb 22 '16 at 16:54

MonkeyZeus · Accepted Answer · 2016-02-22T20:58:10.977

2

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)

edited Feb 22 '16 at 20:58

answered Feb 22 '16 at 17:00

MonkeyZeus

20,375
4
36
77

The problem with converting is that Medoo already fails on knowing and handling this function... – StrikeAgainst Feb 22 '16 at 17:08
1

@StrikeAgainst I think you should check out this function: [**`query()`**](http://medoo.in/api/query) – MonkeyZeus Feb 22 '16 at 20:56
It worked, thank you! Note that I had to use cast instead of convert, since this always got me a SQL error. – StrikeAgainst Feb 23 '16 at 08:50
1

@StrikeAgainst Glad you got it working! I'm no MSSQL expert but I'm glad I pointed you in the right direction :-) – MonkeyZeus Feb 23 '16 at 13:12

score 1 · Answer 2 · answered Feb 22 '16 at 17:49

php has these functions which allow you to convert strings to dateTime objects.

The sql server cast function works on your strings:

select cast ('02.01.2016' as date)

returns 2016-02-01

Use the php function to create datetime variables and pass them to sql server as parameters. Then your query resembles this:

where cast([date] as date) >= @firstParameterFromPHP
and cast([date] as date) < @secondParameterFromPHP

score 0 · Answer 3 · answered Feb 22 '16 at 16:51

0

Since you are comparing strings, 02.01.2016 is bigger than 01.01.2017. You should never store dates as strings, or if you really have to, at least use a format that can be compared. That is, items go from larger to smaller. Then 2016.01.02 will be smaller than 2017.01.01.

But you will still have to use the proper formatting etc. Much easier to just use proper datatypes.

If the existing format is set, you can use CONVERT with style 104 to convert both sides to DATETIME and compare them.

answered Feb 22 '16 at 16:51

Sami Kuhmonen

30,146
9
61
74

As I said, it wasn't my idea to store them like that, I'm gonna have a talk with my admin soon... And I'm not sure if Medoo is capable of CAST and CONVERT. – StrikeAgainst Feb 22 '16 at 16:57
@StrikeAgainst Added a link to documentation about CONVERT, that will help. – Sami Kuhmonen Feb 22 '16 at 16:58
Thanks, but Medoo is a different resort. It doesn't work like standard SQL, but simulates most common SQL features by nesting arrays. Thus it's quite limited with its functions... – StrikeAgainst Feb 22 '16 at 18:02

Date comparison between MSSQL varchar and PHP string does not work - why?

3 Answers3