Excel VB / SQL server:

Question

So this should be an easy one I'm sure.

I have a standard SQL datetime column, I want to grab all entries for the last 24 hours preferably, using vba in excel, ive dumbed the problem down to "just the last calender days" to try and get a better idea on how to solve this and cannot figure out how to proceed.

basically: VBA will accept if I manually enter a date into the "between" part of the sql statement, but won't accept a variable with the date.

Playing around it "appears" that vba is formatting fields declared as dates as mm/dd/yyyy instead of yyyy/mm/dd in a standard Datetime column in sql, which is probably the problem! I've been playing around with the convert command, but to no avail. It does work if I manually enter dates correctly formatted into the variable values though.

code below:

Dim DateVar As Date
Dim DateStart As Date
Dim DateEnd As Date
DateVar = Range("A2").Value
DateStart = DateVar - 1
DateEnd = DateVar + 1

   adoDbConn.Open **CONNECTION STRING GOES HERE**

selectCmd.ActiveConnection = adoDbConn
   selectCmd.CommandText = "SELECT DateTime, Machine_Number, FROM [33_TestImport] 
Where Machine_Number = " & Machvar & " 
AND DateTime BETWEEN " & DateVar & " AND " & DateEnd &  
ORDER By DateTime  "

There shouldn't be a comma here ; Machine_Number, FROM

CDP1802
– CDP1802

2020-03-30 19:28:29 +00:00
Commented Mar 30, 2020 at 19:28 — CDP1802
– CDP1802, Commented Mar 30, 2020 at 19:28

CDP1802 · Accepted Answer · 2020-03-30 19:53:36Z

2

Change variables in the SQL to strings and format them YYYY-MM-DD.

Sub Demo()

    Dim sConStr As String, myDb As Object, rs As Object
    Dim SQL As String, DateYMD As String, Machine As String

    sConStr = "-- connnection string --"

    DateYMD = Format(Range("A2").Value, "YYYY-MM-DD")
    Machine = 123

    SQL = " SELECT DateTime, Machine_Number FROM [33_TestImport]" & _
          " Where Machine_Number = " & Machine & _
          " AND DATEDIFF(minute,DateTime,'" & DateYMD & "') >= 0" & _
          " AND DATEDIFF(minute,DateTime,'" & DateYMD & "') < 1440" ' 24 hours

    Debug.Print SQL

    Set myDb = CreateObject("ADODB.Connection")
    myDb.Open sConStr

    Set rs = myDb.Execute(SQL)
    Range("A3").CopyFromRecordset rs

    myDb.Close

End Sub

answered Mar 30, 2020 at 19:53

CDP1802

17.3k2 gold badges10 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VB / SQL server:

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related