Categories: mysql, sql, excel, vba

Use date in Excel cell in BETWEEN clause in SQL query

3 answers

(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?

All answers to this question, which has the identifier 60639194

The best answer:

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" 

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.

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..

Last questions

how do i remove the switch on my home screen?
how to edit the JS date and time to update atuomatically?
How to utilize data stored in a multidimensional array
Powermockito not mocking URL constructor in URI.toURL() method
Android Bluetooth LE Scanner only scans when phone's Location is turned on in some devices
docker wordpress container can't connect to mysql container
How can I declare a number in java that is more than 64-bits? [duplicate]
Optaplanner solutionClass entityCollectionProperty should never return null error when simple JSON object passed to controller
Anylogic, get the time a pedestrain is in a queue
How do I fix this syntax issue with my .flex file?
Optimizing query in PHP
How to find the highest number of a column and print two columns of that row in R?
Ideas on “Error: Type com.google.firebase.iid.zzav is referenced as an interface from com.google.firebase.messaging.zzd”?
JCIFS SmbFile.exists() and SmbFile.isDirectory() return false when it exists and I can listFiles()
PHP total order
Laravel booking system design
neural net - undefined column selected
How to indicate y axis does not start from 0 in ggplot?
Fragments in backStack
Spinner how to change the data