您的位置: 首页  >  技术问答  >  MSSQL varchar和PHP字符串之间的日期比较不起作用 - 为什么？

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

分类: 技术问答 • 2022-03-03 22:11:59

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

问题描述：

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?

所以我在Microsoft SQL Server 11上运行了一个数据库，表 hpfc_prices code> 按时间顺序包含多个条目，大致如下所示： p> 

  | date（varchar（255））| time（varchar（255））| price（decimal（25）  ，10））| 
 | =================== | =================== | ==  ==================== | 
 | 01.01.2016 | 00:00 | [some value] | 
 | 01.01.2016 | 01:00 | [some 值] | 
 | 01.01.2016 | 02:00 | [有些值] | 
 | ... | ... | ... | 
 | 01.01.2016 | 23:00 | [有些值] |  
 | 02.01.2016 | 00:00 | [some value] | 
 | 02.01.2016 | 01:00 | [some value] | 
 | ... | ... | ... | 
   pre> 
 
 
此表格涵盖2016年1月1日至2020年12月31日， 每个条目代表一个小时。 现在我想在PHP脚本中加载表的一部分，比如2017年全年。 为此我必须使用Medoo作为数据库框架，并且我正在运行以下代码： p> 
 
 


  $ db = new medoo（$ database_config）; 
 $ hpfcStart =“01.01  .2017“; 
 $ hpfcEnd =”31.12.2017“; 
 $ arr = $ db-＆gt; select（”hpfc_prices“，[”date“，”time“，”price“]，[”AND“=  ＆gt; [“date [＆gt; =]”=＆gt; $ hpfcStart，“date [＆lt; =]”=＆gt; $ hpfcEnd]]）; 
var_dump（$ arr）; 
  code>  pre  > 
 
 
 Medoo查询通过调试转换为此SQL查询： p> 
 
 


  SELECT“date”，“time”，“price”FROM“hpfc_prices”  WHERE“date”＆gt; = '01 .01.2017'和“date”＆lt; = '31 .01.2018 
  code>  pre> 
 
 
现在的问题是输出现在包含所有 该表，而不仅仅是2017年的所有条目。我假设查询在日期比较时失败，因为Medoo将字符串与varchar进行比较。 根据我的经验，这适用于MySQL，但我对MSSQL / Medoo不太确定。 谁能告诉我如何正确比较日期？ p> 
  div>

答

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)

答

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.

答

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

相关推荐