带有日期的 Excel 2010 中 VBA 中的 SQL 字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13578633/
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
SQL String in VBA in Excel 2010 with Dates
提问by Willson Fang
I've had a look around but cannot find the issue with this SQL Statement:
我环顾四周，但找不到此 SQL 语句的问题：
strSQL = "SELECT Directory.DisplayName, Department.DisplayName, Call.CallDate, Call.Extension, Call.Duration, Call.CallType, Call.SubType FROM (((Department INNER JOIN Directory ON Department.DepartmentID = Directory.DepartmentID) INNER JOIN Extension ON (Department.DepartmentID = Extension.DepartmentID) AND (Directory.ExtensionID = Extension.ExtensionID)) INNER JOIN Site ON Extension.SiteCode = Site.SiteCode) INNER JOIN Call ON Directory.DirectoryID = Call.DirectoryID WHERE (Call.CallDate)>=27/11/2012"
Regardless of what I change the WHERE it always returns every single value in the database (atleast I assume it does since excel completely hangs when I attempt this) this SQL statement works perfectly fine in Access (if dates have # # around them). Any idea how to fix this, currently trying to create a SQL statement that allows user input on different dates, but have to get over the this random hurdle first.
无论我如何更改 WHERE，它始终返回数据库中的每个值（至少我认为它确实如此，因为当我尝试此操作时 excel 完全挂起）此 SQL 语句在 Access 中运行得非常好（如果日期周围有 ##）。不知道如何解决这个问题，目前正在尝试创建一个允许用户在不同日期输入的 SQL 语句，但必须首先克服这个随机障碍。
EDIT: The date field in the SQL Database is a DD/MM/YY HH:MM:SS format, and this query is done in VBA - EXCEL 2010.
Also to avoid confusion have removed TOP 10 from the statement, that was to stop excel from retrieving every single row in the database.
Current Reference I have activated is: MicrosoftX Data Objects 2.8 Library
Database is a MSSQL, using the connection string:
Provider=SQLOLEDB;Server=#######;Database=#######;User ID=########;Password=########;
编辑：SQL 数据库中的日期字段是 DD/MM/YY HH:MM:SS 格式，此查询是在 VBA - EXCEL 2010 中完成的。
另外为了避免混淆，已从语句中删除了 TOP 10，即停止excel 检索数据库中的每一行。
我激活的当前参考是：MicrosoftX Data Objects 2.8 Library
Database is a MSSQL，使用连接字符串：
Provider=SQLOLEDB;Server=#######;Database=#######;User ID=# #######；密码=########；
回答by shahkalpesh
WHERE (Call.CallDate) >= #27/11/2012#
Surround the date variable with #.
用 包围日期变量#。
EDIT: Please make date string unambiguous, such as 27-Nov-2012
编辑：请明确日期字符串，例如 27-Nov-2012
strSQL = "SELECT ........ WHERE myDate >= #" & Format(dateVar, "dd-mmm-yyyy") & "# "
If you are using ado, you should look at Paramatersinstead of using dynamic query.
如果您正在使用 ado，您应该查看Paramaters而不是使用动态查询。
EDIT2: Thanks to @ElectricLlama for pointing out that it is SQL Server, not MS-Access
EDIT2：感谢@ElectricLlama 指出它是 SQL Server，而不是 MS-Access
strSQL = "SELECT ........ WHERE myDate >= '" & Format(dateVar, "mm/dd/yyyy") & "' "
回答by Nick.McDermaid
Please verify that the field Call.CallDate is of datatype DATETIME or DATE
请验证字段 Call.CallDate 的数据类型为 DATETIME 或 DATE
If you are indeed running this against SQL Server, try this syntax for starters:
如果您确实在 SQL Server 上运行它，请为初学者尝试以下语法：
SELECT Directory.DisplayName, Department.DisplayName, Call.CallDate,
Call.Extension, Call.Duration, Call.CallType, Call.SubType
FROM (((Department INNER JOIN Directory
ON Department.DepartmentID = Directory.DepartmentID)
INNER JOIN Extension ON (Department.DepartmentID = Extension.DepartmentID)
AND (Directory.ExtensionID = Extension.ExtensionID))
INNER JOIN Site ON Extension.SiteCode = Site.SiteCode)
INNER JOIN Call ON Directory.DirectoryID = Call.DirectoryID
WHERE (Call.CallDate)>= '2012-11-27'
The date format you see is simply whatever format your client tool decides to show it in. Dates are not stored in any format, they are effectively stored as a duration since x.
您看到的日期格式只是您的客户端工具决定显示的任何格式。日期不以任何格式存储，它们有效地存储为自 x 以来的持续时间。
By default SQL Uses the format YYYY-MM-DD if you want to use a date literal.
默认情况下，如果您想使用日期文字，SQL 使用格式 YYYY-MM-DD。
But you are much better off defining a parameter of type date in your code and keeping your date a data type 'date' for as long as possible. This may include only allowing them to enter the date using a calendar control to stop ambiguities.
但是您最好在代码中定义一个日期类型的参数并尽可能长时间地将日期保持为数据类型“日期”。这可能包括只允许他们使用日历控件输入日期以消除歧义。
