Excel 2010中的VBA中的SQL字符串与日期 [英] SQL String in VBA in Excel 2010 with Dates

问题描述

我已经看了一下，但是找不到这个SQL语句的问题：

  strSQL =SELECT Directory DisplayName，Department.DisplayName，Call.CallDate，Call.Extension，Call.Duration，Call.CallType，Call.SubType FROM（（（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
  

无论我更改WHERE，它总是返回数据库中的每个值至少我认为它是因为excel完全挂起，当我尝试这个）这个SQL语句在Access中工作得很好（如果日期有＃＃周围）。任何想法如何解决这个问题，目前正在尝试创建一个允许用户在不同日期输入的SQL语句，但必须首先解决这个随机障碍。

编辑：SQL数据库中的日期字段是DD / MM / YY HH：MM：SS格式，此查询在VBA - EXCEL 2010中完成。

也避免混淆已经从声明中删除了TOP 10，即停止从检索数据库中的每一行执行excel。

当前参考我激活的是：MicrosoftX数据对象2.8库

数据库是一个MSSQL，使用连接字符串：

Provider = SQLOLEDB; Server = #######; Database = #######; User ID = ######## ;密码= ########;

解决方案

请验证Call.CallDate字段是否为数据类型DATETIME或DATE

如果您确实针对SQL Server运行此操作，请尝试使用此起始语法：

  SELECT Directory.DisplayName，Department.Displa yName，Call.CallDate，
 Call.Extension，Call.Duration，Call.CallType，Call.SubType 
 FROM（（（INNER JOIN Directory 
 ON Department.DepartmentID = Directory.DepartmentID） 
 INNER JOIN扩展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'
  

您所看到的日期格式只是您客户端工具决定显示的任何格式。日期不以任何格式存储，它们有效地存储为x的持续时间。 p>

默认值如果要使用日期文字，则使用格式YYYY-MM-DD。

但是在代码中定义类型日期的参数，并尽可能长的保持数据类型日期更好。这可能包括只允许他们使用日历控件输入日期来阻止模糊。

I've had a look around but cannot find the issue with this SQL Statement:

    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.

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=########;

解决方案

Please verify that the field Call.CallDate is of datatype DATETIME or DATE

If you are indeed running this against SQL Server, try this syntax for starters:

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.

By default SQL Uses the format YYYY-MM-DD if you want to use a date literal.

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.

这篇关于Excel 2010中的VBA中的SQL字符串与日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！