列中包含当前日期的 VBA 查询数据

Question

我正在尝试用VBA编写一条查询语句，以从DB2中提取数据。DAILY_REPORT表中的一列是DATE，其格式为"5/20/2020 7:55:01 AM"。我感兴趣的是提取与今天的日期相对应的无小时数据。下面给了我需要的结果，但日期必须是硬输入的。如何使查询动态？关于使用GETDATE()函数的思考。

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

谢谢大家的帮助。

Answer 1

了解到您正在正确地转换未格式化的日期，您可以尝试CURRENT date:

WHERE cast(MY_DATE as date)= CURRENT DATE

如果转换不起作用，可以使用函数TO_DATE((+DATE((:

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