sql-server - 来自 SQL 查询的错误格式转换

Question

当我使用查询过滤列DATE_INSERT（类型DATETIME）时，我遇到了一个奇怪的问题。

我尝试了这两个查询：

查询 #1：

SELECT 
    FORMAT(DATE_INSERT, 'dd/MM/yyyy hh:mm:ss') as DATA_INSERIMENTO, 
    COD_ART_PF, DESCR_PF, LOTTO_PF, COD_MP, DESCR_MP, LOTTO_MP, QTA_MP, 
    CAPO_TURNO 
FROM
    ZZ_ANABASIPRODUCO_MP 
WHERE
    LOTTO_MP = '0' 
    AND QTA_MP <> '0' 
    AND DATA_INSERIMENTO BETWEEN '" & DateTimePicker1.Value & "' AND '" & DateTimePicker2.Value & "' 
ORDER BY
    LOTTO_PF

查询 #2：

WITH CTE AS
(
    SELECT 
        FORMAT(DATE_INSERT, 'dd/MM/yyyy hh:mm:ss') AS DATA_INSERIMENTO, 
        COD_ART_PF, DESCR_PF, LOTTO_PF, COD_MP, DESCR_MP, LOTTO_MP, QTA_MP, 
        CAPO_TURNO
    FROM 
        ZZ_ANABASIPRODUCO_MP
)
SELECT
    DATA_INSERIMENTO, DATA_INSERIMENTO, COD_ART_PF, DESCR_PF, LOTTO_PF, 
    COD_MP, DESCR_MP, LOTTO_MP, QTA_MP, CAPO_TURNO
FROM 
    CTE
WHERE 
    LOTTO_MP = '0' 
    AND QTA_MP <> '0' 
    AND DATA_INSERIMENTO BETWEEN '01/07/2019 00:00:00' AND '03/07/2019 23:59:59'

但是结果总是错的，就像把日期和月份颠倒了一样，即使你格式正确

这是两个查询的结果：

| DATA_INSERIMENTO | LOTTO_PF      |
+------------------+---------------+
| 03/01/2019       | 040585190701B |
| 03/01/2019       | 060428190701B |
| 02/02/2019       | 040585190701B |
| 02/02/2019       | 040585190701C |
| 02/02/2019       | 040585190701C |
| 02/02/2019       | 060528190701C |
| 02/02/2019       | 030330190702A |

结果很多，我不能全部列出，但我可以告诉你，月份从 01 到 07，天从 01 到 03，如表中所示。

标签： sql-serverdatetime