sql - 通过 VBA 将日期格式更改为 SQL 查询

Question

我有一个无法回答的问题，我的问题是我有一个宏，它使用 SQL 查找来访问 SQL 数据库并提取信息。在 excel 中，它总是强制日期为 DD/MM/YYYY(您可以强制格式化，但是当它传递到 SQL 端时，即使格式在视觉上看起来正确，它仍然会显示为 DD/MM/YYYY)。

我已经尝试对我的代码进行一些更改以尝试“强制”它，但是我没有运气，我是否过度复杂化了它，或者它应该更难做到？哈哈。

我将提供我的 VB 代码和“属性”，以了解使用宏创建的“连接”的样子。

请注意，格式需要为 YYYY-MM-DD，因为这是它在数据库中的存储方式。目前解决它的唯一方法是在日期前使用“'”，例如 '2013-12-01 强制它，否则它会转到 2013 年 1 月 12 日。

有任何想法吗？我已经绞尽脑汁太久了，哈哈。

问候杰米

如果需要该信息，服务器是 SQLEXPRESS 服务器。

下面的代码:

Sub CustomisedSQLQuery()
'
' SQL Query to allow user customisation. 
'

'
Dim FileName As String
Dim User As String
Dim StartDate As String
Dim EndDate As String
Dim Category As String


Dim Confirm As Variant
Confirm = MsgBox("Have you made sure that at least one of the search criteria's is populated? If so your excel may crash or you may kill the database.", vbQuestion + vbYesNo, "Wait....")
If Confirm = vbNo Then ActiveWorkbook.Sheets("Input Sheet").Activate
If Confirm = vbNo Then Exit Sub



FileName = Worksheets("Input Sheet").Cells(10, 1)
User = Worksheets("Master DATA").Cells(1, 1)
StartDate = Worksheets("Input Sheet").Cells(10, 3)
EndDate = Worksheets("Input Sheet").Cells(10, 4)
Category = Worksheets("Master DATA").Cells(1, 5)

MyStr = Format(StartDate, "yyyy/mm/dd")
MyStr = Format(EndDate, "yyyy/mm/dd")

    Sheets("Output Sheet").Select
    Cells.Select
    Selection.ClearContents
    Range("A1").Select
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "ODBC;DRIVER=SQL Server;SERVER=SERVERADDRESS;UID=USERNAME;PWD=PASSWORD;APP=Microsoft Office 2010;WSID=ID" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandText = Array( _
        "SELECT DocumentsRead.userID, DocumentsRead.fileName, DocumentsRead.category, DocumentsRead.downloadedByUser, DocumentsRead.timeDownloaded, DocumentsRead.timeR" _
        , _
        "ead" & Chr(13) & "" & Chr(10) & "FROM EndUsers.dbo.DocumentsRead DocumentsRead" & Chr(13) & "" & Chr(10) & "WHERE (DocumentsRead.fileName Like '" & FileName & "') AND (DocumentsRead.category='" & Category & "') AND (DocumentsRead.timeRead Is Null) " _
        , "AND (DocumentsRead.timeDownloaded Between {ts '" & StartDate & " 00:00:01'} An" _
        , "d {ts '" & EndDate & " 00:00:01'})")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With

Sheets("Input Sheet").Select
End Sub

这是来自 SQL 的连接属性

SELECT DocumentsRead.userID, DocumentsRead.fileName, DocumentsRead.category, DocumentsRead.downloadedByUser, DocumentsRead.timeDownloaded, DocumentsRead.timeRead
FROM EndUsers.dbo.DocumentsRead DocumentsRead
WHERE (DocumentsRead.fileName Like 'GB') AND (DocumentsRead.category='Notices') AND (DocumentsRead.timeRead Is Null) AND (DocumentsRead.timeDownloaded Between {ts '01/12/2013 00:00:01'} And {ts '08/11/2013 00:00:01'})

输入表如下所示:

Answer 1

看起来您的问题出在 StartDate/EndDate 的格式上。这是您的代码:

MyStr = Format(StartDate, "yyyy/mm/dd")
MyStr = Format(EndDate, "yyyy/mm/dd")

这是我假设您想要做的事情:

StartDate = Format(StartDate, "yyyy-mm-dd")
EndDate = Format(EndDate, "yyyy-mm-dd")

我相信您还可以通过将字符串包装在 # 中来告诉 Access 字符串是日期。如

"and DocumentsRead.timeDownloaded >= #" & StartDate & "#" & vbcrlf & _
"and DocumentsRead.timeDownloaded <  #" & EndDate & "#"