通过 VBA 将日期格式更改为 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21189434/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Changing date format via VBA to SQL query
提问by JamieB
I have a question which I haven't been able to answer to far, my issue is that I have a macro that is using a SQL lookup to access a SQL database and pull information. Within excel it is always forcing the dates to DD/MM/YYYY (You can force the formatting but when it passes it to the SQL side of things it still comes out as DD/MM/YYYY even if the formatting visually looks correct).
我有一个一直无法回答的问题，我的问题是我有一个使用 SQL 查找来访问 SQL 数据库并提取信息的宏。在 excel 中，它总是强制日期为 DD/MM/YYYY（您可以强制格式化，但是当它传递到事物的 SQL 端时，即使格式在视觉上看起来正确，它仍然显示为 DD/MM/YYYY）。
I have tried a number of changes to my code to try and 'force' it however I have had no luck am I over complicating it or is it alot harder to do that it should be? lol.
我已经尝试对我的代码进行一些更改以尝试“强制”它，但是我没有运气是我把它复杂化了还是它应该更难做到？哈哈。
I will provide my VB code and the "Properties" for what the 'connection' looks like that is made from using the macro.
我将提供我的 VB 代码和“属性”，说明使用宏创建的“连接”的样子。
As a note, the format needs to be YYYY-MM-DD as this is how its stored within the database. Currently the only way to get around it is to use a " ' " infront of the date for instance '2013-12-01 to force it or else it goes to 01/12/2013.
请注意，格式必须是 YYYY-MM-DD，因为这是它在数据库中的存储方式。目前绕过它的唯一方法是在日期前使用“'”，例如 '2013-12-01 来强制它，否则它会转到 01/12/2013。
Any ideas? I have been racking my brain for far too long lol.
有任何想法吗？我已经绞尽脑汁太久了哈哈。
Regards Jamie
问候杰米
Server is a SQLEXPRESS server if that info is needed.
如果需要该信息，服务器是 SQLEXPRESS 服务器。
Code below:
代码如下：
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")).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
This is the connection properties from SQL
这是来自 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'})
The input sheet looks as follows:
输入表如下所示：
回答by BAReese
It looks like your problem is in the formatting of the StartDate/EndDate. Here is your code:
看起来您的问题出在 StartDate/EndDate 的格式上。这是你的代码：
MyStr = Format(StartDate, "yyyy/mm/dd")
MyStr = Format(EndDate, "yyyy/mm/dd")
Here is what I assume you want to be doing:
这是我假设您想要做的事情：
StartDate = Format(StartDate, "yyyy-mm-dd")
EndDate = Format(EndDate, "yyyy-mm-dd")
I believe you can also tell Access the string is a date by wrapping it in #. Such as
我相信您也可以通过将字符串包装在 # 中来告诉 Access 字符串是一个日期。如
"and DocumentsRead.timeDownloaded >= #" & StartDate & "#" & vbcrlf & _
"and DocumentsRead.timeDownloaded < #" & EndDate & "#"
