vba 如何在 SQL 查询中格式化日期？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27819611/
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
How to format date in a SQL query?
提问by Amen Jlili
Hello I'm using MS JET OLEDB 4.0 on VBA (MS ACCESS)
你好，我在 VBA (MS ACCESS) 上使用 MS JET OLEDB 4.0
SQL = "SELECT COUNT(Date) FROM [Orders] WHERE [Orders].[Date] BETWEEN " & "#" & StartDate & "#" & " AND " & "#" & EndDate & "#"
The [Date] is formatted like f dd/mm/yyyy (French) and the StartDate and EndDate are formatted like mm/dd/yyyy (Two date objects).
[Date] 的格式为 f dd/mm/yyyy（法语），StartDate 和 EndDate 的格式为 mm/dd/yyyy（两个日期对象）。
My belief is that BETWEEN only compares dates formatted mm/dd/yyyy, so is there a way like a function to convert the [Date] formatting into mm/dd/yyyy so the between function can compare correctly?
我的信念是 BETWEEN 只比较格式为 mm/dd/yyyy 的日期，那么有没有一种方法可以将 [Date] 格式转换为 mm/dd/yyyy 以便函数之间可以正确比较？
Edit: Using a string instead of the dates like follow:
编辑：使用字符串代替日期，如下所示：
StartDateFormatted = Format(StartDate,"dd/mm/yyyy")
EndDateFormatted = Format(EndDate,"dd/mm/yyyy")
So as to be sure of the startdate and enddate format. It doesn't work still.
以确保开始日期和结束日期格式。它仍然不起作用。
I'm left to assume two things:
我不得不假设两件事：
Either BETWEEN onlys compares mm/dd/yyyy formats
I have to use a function to format [Date] to mm/dd/yyyy
BETWEEN only 比较 mm/dd/yyyy 格式
我必须使用函数将 [Date] 格式化为 mm/dd/yyyy
Thanks to @Maciej Los for answering my question.
感谢@Maciej Los 回答我的问题。
回答by i486
Always use ISO date formatting YYYY-MM-DD and you will have no problems. Without formatting.
始终使用 ISO 日期格式 YYYY-MM-DD，您不会有任何问题。无需格式化。
回答by George Tomeh
I have noticed from the SQL compilation of the query when using Between, that it turns it to MM/DD/YYYY and it did not work till I used Format(startdate, "mm/dd/yyyy"). Please check the snapshop.
我在使用Between时从查询的SQL编译中注意到，它将它转换为MM/DD/YYYY，并且在我使用Format(startdate, "mm/dd/yyyy")之前它没有工作。请检查快照商店。
回答by Maciej Los
Use Format function.
使用格式功能。
SQL = "SELECT COUNT(Date)" & vbcr & _
"FROM [Orders]" & vbcr & _
"WHERE [Orders].[Date] BETWEEN #Format(" & StartDate & "], 'MM/dd/yyyy')# AND #Format(" & EndDate & ",'MM/dd/yyyy')#"
