Ubuntu上MSSQL查询的空结果集

Question

我在带有PHP 7.2的Ubuntu 18.04上使用sqlsrv模块来运行以下sql查询

SELECT REPLACE(LTRIM(REPLACE(CardNo, '0', ' ')),' ', '0') AS staff_id,
FORMAT(PunchDatetime, 'yyyy-MM-dd') as 'date',
FORMAT(min(PunchDatetime), 'HH:mm:ss') AS time_in,
IIF(min(PunchDatetime) != max(PunchDatetime), FORMAT(max(PunchDatetime), 'HH:mm:ss'), NULL) AS time_out
FROM PunchRecords
WHERE cast('2018-11-01' as date) = cast(FORMAT(PunchDatetime, 'yyyy-MM-dd') as date)
group by CardNo, FORMAT(PunchDatetime, 'yyyy-MM-dd')
order by CardNo";

在Windows上执行的相同查询从表中返回数据，但在Ubuntu环境中运行时返回空的结果集。

对该问题的任何反馈都将不胜感激。

Answer 1

最明显的原因可能是Ubuntu上的日期格式不同2018-11-01’可以解释为YYYY-MM-DD或YYYY-DD-MM。只需检查一下：

SET LANGUAGE ENGLISH;
SELECT CAST('2014-09-13' AS DATETIME);
GO
SET LANGUAGE GERMAN;
SELECT CAST('2014-09-13' AS DATETIME);--ERROR: there's no month "13"
GO

比较日期时，只需比较日期，不要将其转换为字符串。简单使用：

WHERE '20181101' = cast(PunchDatetime as date)