SQL查询中BETWEEN子句中Excel单元格中的使用日期

Question

（1）表

CREATE TABLE logistics (
    id int primary key,
    Product VARCHAR(255),
    insert_timestamp Date
);

INSERT INTO logistics
(id, product, insert_timestamp
)
VALUES 
("1", "Product_A", "2020-02-24 18:15:48"),
("2", "Product_B", "2020-02-24 20:30:17"),
("3", "Product_C", "2020-02-24 23:54:58"),
("4", "Product_D", "2020-02-25 08:09:30"),
("5", "Product_E", "2020-02-25 10:17:15");

（2）中的VBA
值Cell B1 = 2020-02-24

Sub Get_Data()

Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim dateVar As Date

    Set conn = New ADODB.Connection
    conn.ConnectionString = "DRIVER={MySQL ODBC 5.1 Driver}; SERVER=localhost; DATABASE=bi; UID=username; PWD=password; OPTION=3"
    conn.Open

                strSQL = " SELECT " & _
                            " cID " & _
                            " FROM logistics " & _
                            " WHERE DATE(insert_timestamp) BETWEEN """ & Format(Sheet1.Range("B1").Value, "YYYY-MM-DD") & "00:00:00" & """ " & _
                            " AND """ & Format(Sheet1.Range("B1").Value, "YYYY-MM-DD") & "23:59:59" & """ " & _
                            " GROUP BY 1 "


    Set rs = New ADODB.Recordset
    rs.Open strSQL, conn, adOpenStatic

    Sheet1.Range("A1").CopyFromRecordset rs

    rs.Close
    conn.Close

End Sub

我想在子句中的语句中VBA使用日期的Cell B1内部运行查询。 当我在之外运行此查询时，它运行完美：BETWEEN``WHERE
VBA

SELECT
Product
FROM logistics
WHERE DATE(insert_timestamp) BETWEEN "2020-02-24 00:00:00" AND "2020-02-24 23:59:59";

一次，我在中运行它VBA不会给我一个错误，但也不会给我任何结果。
我认为问题是由sql和中的组合引起VBA的strSQL。

我必须如何更改VBA才能使其正常工作？

Answer 1

您在日期和时间之间缺少空格…

打开VBE并调试打印公式以查看结果（制造商确保您具有立即窗口[视图菜单/立即窗口]）。

Sub test()
   Debug.Print Format(Sheet1.Range("B1").Value, "YYYY-MM-DD") & "23:59:59"
End Sub

结果 2020-03-1123：59：59

只需在DD之后添加空格即可，如下

Format(Sheet1.Range("B1").Value, "YYYY-MM-DD ") & "23:59:59"