sql-server - 将vba中的日期变量传递给sql语句

Question

我查看了已发布的各种不同场景，但似乎没有一个解决方案能带来任何快乐。我正在尝试将日期变量传递到 sql select 语句中，但我收到有关转换为日期/时间的错误，或者变量未正确定义的事实，即语法不正确。这同样适用于变量“contractNumber”，但我希望日期变量完成后，同样适用于“contractNumber”变量。我尝试用“”/“&/#””围绕变量，但这些组合似乎都不起作用....

我使用的代码如下，“控制”表中的日期是标准的英国日期 - DD/MM/YYYY

Sub MEHT()

Dim startdate As Date
Dim enddate As Date
Dim contractNumber As String

startdate = Sheets("Control").Range("K8").Value
enddate = Sheets("Control").Range("K10").Value
contractNumber = Sheets("Control").Range("K13").Value

Call ConnectSqlServer

End Sub

Sub ConnectSqlServer()

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sConnString As String

    ' Create the connection string.
    sConnString = "Provider=SQLOLEDB;Data Source=NT124431\DW;" & _
                  "Initial Catalog=hdw2;" & _
                  "Integrated Security=SSPI;"


    ' Create the Connection and Recordset objects.
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    ' Open the connection and execute.
    conn.Open sConnString
    Set rs = conn.Execute("SELECT SAF.Tracking_Number, ORH.CUSTOMER_PO_NUMBER,MAX(DOC.DOCUMENT_NUMBER),DOC.DOCUMENT_DATE_KEY,SAF.ITEM_NUMBER,  MAX(ITM.HOMECRAFT_ITEM_NUMBER), " _
        & "MAX(ITM.ITEM_DESCR),MAX(ADR.ADDR_NAME),MAX(ADR.ADDR1),MAX(ADR.ZIP_5_KEY),SUM(SAF.QUANTITY_SOLD),SUM(SAF.EXTENDED_AMOUNT) " _
        & ",SUM(SAF.EXTENDED_AMOUNT) / SUM(SAF.QUANTITY_SOLD) FROM SALES_FACT SAF  JOIN LU_CUSTOMER_TBL CUS ON SAF.CUSTOMER_KEY = CUS.CUSTOMER_KEY " _
        & "JOIN ORDER_DM ORH ON SAF.ORDER_KEY = ORH.ORDER_KEY JOIN PRODUCT_DM PRD ON SAF.PRODUCT_KEY = PRD.PRODUCT_KEY JOIN DOCUMENT_DM DOC " _
        & "ON SAF.DOCUMENT_KEY = DOC.DOCUMENT_KEY JOIN LU_ITEM ITM ON SAF.ITEM_NUMBER = ITM.ITEM_NUMBER JOIN LU_ADDRESS ADR ON DOC.SHIP_TO_ADDRESS_KEY = ADR.ADDRESS_KEY " _
        & "WHERE ITM.ITEM_CATEGORY_KEY NOT IN ('31010') AND SAF.TRACKING_CODE NOT IN ('F') AND PRD.PRODUCT_LINE_KEY NOT IN ('UN') AND PRD.PRODUCT_SUB_LINE_KEY NOT IN ('60P') " _
        & "AND SAF.TRACKING_NUMBER = " & contractNumber & " AND SAF.EXTENDED_AMOUNT > 0 AND DOC.DOCUMENT_DATE_KEY BETWEEN CONVERT(Date, " & startdate & " , 120) AND CONVERT(Date," & enddate & " , 120) " _
        & "GROUP BY ORH.CUSTOMER_PO_NUMBER, DOC.DOCUMENT_DATE_KEY,SAF.ITEM_NUMBER,SAF.Tracking_Number ORDER BY SAF.Tracking_Number,DOC.DOCUMENT_DATE_KEY")

    ' Check we have data.
    If Not rs.EOF Then
        ' Transfer result.
        Sheets("Detail").Range("A1").CopyFromRecordset rs
    ' Close the recordset
        rs.Close
    Else
        MsgBox "Error: No records returned.", vbCritical
    End If

    ' Clean up
    If CBool(conn.State And adStateOpen) Then conn.Close
    Set conn = Nothing
    Set rs = Nothing

End Sub

Answer 1

The code I am using is below and the dates in the "Control" sheets are standard UK date - DD/MM/YYYY

尽管我是英国人，但我不会推荐使用您的日期格式...

将日期传递给 SQL Server 的最安全方法是使用“yyyy-MM-dd”。这保证了无论您身在世界何处，它都能正常工作。

select * from [SomeTable]
where Update_Time >= '2015-08-06'