CODEWITHSUNDEEP
sqlexcelvba
firezen
firezen

Reputation: 15

VBA query data containing today's date in a column

I am trying to write a query statement in VBA to extract data from DB2. One of the columns in DAILY_REPORT table is DATE which has the format '5/20/2020 7:55:01 AM'. I am interested in extracting data corresponding to today's date without hours. Below gives me the results i need but date has to be hard typed. How do I make the query dynamic? Thinking of using GETDATE() function.

   Sub Report()


   Dim NSQL As String
   Dim conn As Object
   Dim rstRecordset As Object

   Const connPath As String = "DSN=;UID=;PWD=;DBALIAS="
   Set conn = CreateObject("ADODB.Connection")
   conn.Open connPath
   
   NSQL = ""
   NSQL = NSQL & "SELECT TECH,ID,MY_DATE FROM DAILY_REPORT WHERE cast(MY_DATE as date)= '2021-07-14'"
   
Set rstRecordset = CreateObject("ADODB.Recordset")
   rstRecordset.Open _
       Source:=NSQL, _
       ActiveConnection:=conn, _
       CursorType:=adOpenStatic, _
       LockType:=adLockReadOnly, _
       Options:=adCmdText
   
   With ActiveSheet.QueryTables.Add( _
           Connection:=rstRecordset, _
           Destination:=Range("A1"))
       .FieldNames = True
       .RowNumbers = False
       .FillAdjacentFormulas = False
       .PreserveFormatting = True
       .RefreshOnFileOpen = False
       .BackgroundQuery = True
       .RefreshStyle = xlInsertDeleteCells
       .SavePassword = True
       .SaveData = True
       .AdjustColumnWidth = True
       .RefreshPeriod = 0
       .PreserveColumnInfo = True
       .Refresh BackgroundQuery:=False
   End With


rstRecordset.Close
Set rstRecordset = Nothing
conn.Close
Set conn = Nothing

Sheets("Data").Visible = True

Call SaveAsCSV_Data

End Sub

Thanks all for the help.

Upvotes: 0

Views: 225

Answers (1)

Aleix CC
Aleix CC

Reputation: 2089

Understanding that you are casting the unformatted date correctly, you could try CURRENT DATE:

WHERE cast(MY_DATE as date)= CURRENT DATE

In case your cast doesn't work, you can use the functions TO_DATE() + DATE():

WHERE 
  DATE(TO_DATE(MY_DATE, 'MM/DD/YYYY HH:MI:SS AM')) = CURRENT DATE

Upvotes: 1

Related Questions