SQL查询中BETWEEN子句中Excel单元格中的使用日期 [英] Use date in Excel cell in BETWEEN clause in SQL query

问题描述

(1)表 DB-Fiddle

(1) Table DB-Fiddle

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
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 中运行查询，该查询使用 WHERE中 BETWEEN 语句中 B1单元格中的日期子句.
当我在 VBA 之外运行此查询时，它运行良好:

I want to run a query within the VBA that uses the date in Cell B1 within the BETWEEN statement in the WHERE clause.
When I run this query outside of the VBA it works perfectly:

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

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

Once, I run it in the VBA it does not give me an error but it also does not give me any result.
I assume the issue is caused by my combination of the sql and the VBA in the strSQL.

如何更改 VBA 以使其正常工作?

How do I have to change the VBA to make it work?

推荐答案

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

you are missing a space between the date and the time...

打开VBE并调试打印公式以查看结果(确保您具有立即窗口[视图菜单/立即窗口].

Open your VBE and debug print the formula to see the result (maker sure you have the immediate window [view menu / Immediate window).

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

结果2020-03-1123:59:59

result 2020-03-1123:59:59

只需在DD后面添加空格，如下所示

Just can add the space after the DD as follow

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

这篇关于SQL查询中BETWEEN子句中Excel单元格中的使用日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！