Use date in Excel cell in BETWEEN clause in SQL query

Question

(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
Value in 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

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";

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.

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

While the accepted answer is working, you should invest some time in learning how to use ADODB.Parameter and ADODB.Command to pass parameters to a query. No more hassle with formatting, no issues with differences in databases (your example will not work with Oracle) and a protection against SQL injection. — FunThomas
– FunThomas, Commented Mar 11, 2020 at 15:38

Parfait · Accepted Answer · 2020-03-11 16:14:59Z

Instead of formatting values like dates or strings into the SQL command, it is much better to use ADODB.Parameter - in that case the driver will do all the work for you. You don't have to take care about quotes around a string, formatting a date so that the database understands it correctly (which is highly depending on database, regional settings and so on). Plus it is a protection against SQL injection. Plus, the query optimizer can do it's job much better because it gets the same SQL command every time and remembers the execution plan.

Drawback: code get's slightly more complicated because you have to involve a ADODB.command object.

In your SQL statement, you put a simple ? at the place where you want to have a parameter. You just have to take care that the numbers and the position of ? and parameters matches.

Dim Conn As New ADODB.Connection, cmd As New ADODB.Command, param As ADODB.Parameter, rs As ADODB.Recordset
Conn.Open "<your connection string>"
Set cmd.ActiveConnection = Conn
cmd.CommandText = "SELECT cID FROM logistics WHERE DATE(insert_timestamp) BETWEEN ? AND ? GROUP BY 1"

Set param = cmd.CreateParameter(, adDate, adParamInput, , Date)
cmd.Parameters.Append param
Set param = cmd.CreateParameter(, adDate, adParamInput, , Date + 1)
cmd.Parameters.Append param

Set rs = cmd.Execute
Debug.Print rs.Fields(0).Name, rs(0).Value

P.S. Was a little lazy for the date handling, if you have data exactly at midnight, you would get too much data.

Jose MMMH · Accepted Answer · 2020-03-11 15:24:13Z

1

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

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

result 2020-03-1123:59:59

Just can add the space after the DD as follow

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

answered Mar 11, 2020 at 15:24

Jose MMMH

813 bronze badges

2 Comments

Michi Over a year ago

Exactly what I needed. Thanks a lot.

Parfait Over a year ago

Or avoid using BETWEEN and run the less than operator: < without the need of 23:59:59 concatenation.

CLR · Accepted Answer · 2020-03-11 15:37:53Z

I know you've accepted an answer and it's a perfectly good one. I'm just leaving this here as an alternative method which you might find useful in other cases too:

Dim date_to_use As String

date_to_use = Format(Sheet1.Range("B1").Value, "YYYY-MM-DD")

strSQl = " SELECT " & _
            " cID " & _
            " FROM logistics " & _
            " WHERE DATE(insert_timestamp) BETWEEN '[date to use] 00:00:00'" & _
            " AND '[date to use] 23:59:59'" & _
            " GROUP BY 1 "

strSQl = Replace(strSQl, "[date to use]", date_to_use)

Storing the contents of B1 in this way before using it also allows you to apply other changes to it - just in case you wanted to clean it up further or reduce the chances of SQL injection being used..

Collectives™ on Stack Overflow

Use date in Excel cell in BETWEEN clause in SQL query

3 Answers 3

Comments

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Linked

Related