比较Access中SQL Server链接表中的日期 [英] Comparing dates from SQL Server linked table in Access
问题描述
我在Access中有一个SQL Server链接表，我正在尝试查询以提取特定日期或数据范围（即< 01/31/2017 ，例如）。我在Access设计模式下尝试使用以下条件：< ＃01/31/2017＃，但无法正确比较。
I have a SQL Server linked table in Access and I am trying to query to extract from a certain date or data ranges (i.e. < 01/31/2017, for example). I tried in the criteria in Access design mode, the following: < #01/31/2017# but it is not comparing correctly.
SQL Server表中的字段为日期时间数据类型，我格式化了在设计模式下的属性中，将其作为短日期访问。
The field in the SQL server table is datetime data type, I formatted it in Access as Short Date in the properties in design mode.
SELECT dbo_LicensesLiveViewWithRevenue.BAN,
dbo_LicensesLiveViewWithRevenue.PTN,
dbo_LicensesLiveViewWithRevenue.SOC,
dbo_LicensesLiveViewWithRevenue.LicenseCreatedOn,
Format([DeactivationDate],"Short Date") AS DeactOn,
dbo_LicensesLiveViewWithRevenue.RetailPrice,
dbo_LicensesLiveViewWithRevenue.WholeSalePrice,
dbo_LicensesLiveViewWithRevenue.AccountID
FROM dbo_LicensesLiveViewWithRevenue
WHERE (((dbo_LicensesLiveViewWithRevenue.LicenseCreatedOn)< #2017/01/31#)) ORDER BY dbo_LicensesLiveViewWithRevenue.LicenseCreatedOn;
我也尝试通过将日期作为参数传递给表单的查询，但是没有运气。
I also tried the query with passing the dates as parameters from a form, with no luck.
SELECT dbo_LicensesLiveViewWithRevenue.BAN,
dbo_LicensesLiveViewWithRevenue.PTN,
dbo_LicensesLiveViewWithRevenue.SOC,
dbo_LicensesLiveViewWithRevenue.LicenseCreatedOn,
Format([DeactivationDate],"Short Date") AS DeactOn,
dbo_LicensesLiveViewWithRevenue.RetailPrice,
dbo_LicensesLiveViewWithRevenue.WholeSalePrice,
dbo_LicensesLiveViewWithRevenue.AccountID
FROM dbo_LicensesLiveViewWithRevenue
WHERE (((dbo_LicensesLiveViewWithRevenue.LicenseCreatedOn)<[Forms]![MainForm]![EndDate]));
结果：
当我直接在SQL Server中运行查询时，会为我提供具有正确日期的正确记录（在SQL中，日期是<'20170131'）。在Access中，我有相同的记录数，但是日期显示为错误，如结果图片中所示。
When I run the query directly in SQL server is giving me the correct records with correct dates (in SQL the dates are < '20170131'). In Access I have the same count of records but the dates are showing wrong as in the result pictures.
推荐答案
我建议您在设计视图中打开链接表。忽略有关此操作为只读的提示消息。
I would suggest that you open the linked table in design view. Ignore the prompt msg about this action being read only).
如果所讨论的列是较新的日期时间格式（datetime2），并且您使用标准的SQL驱动程序进行了链接，这样的日期将被视为TEXT列，而不是日期/时间。
If the columns in question are newer date time formats (datetime2), and you link using the standard SQL driver, then such date are seen as TEXT columns, and NOT date/time.
如果通过访问将列视为日期或文本列。
A quick look at the linked table in design view will revel if the columns are seen by access as date or text columns.
如果您使用SQL Server中任何较新的格式日期，则在将表链接到SQL时必须使用较新的本机11（或更高版本）驱动程序服务器。出于这个原因，我倾向于坚持使用较旧的默认旧版SQL驱动程序。旧版驱动程序已安装并包含在Windows中-因此，在每个工作站上运行Access时不需要安装驱动程序。
If you are using any newer format dates from SQL server, you MUST use the newer native 11 (or later) drivers when you link tables to SQL server. It is for this reason that I tend to stick with the older "default" legacy SQL driver. The legacy drivers are installed and included with windows - and thus no install of drivers is required when you run Access on each workstation.
但是，如果您使用的是较新的日期格式在SQL Server中，则需要使用本机11驱动程序链接表，并且除了Access（或Access运行时）之外，还必须在每个工作站上分发和安装本机11驱动程序。因此，尽管应该首选并使用较新的SQL驱动程序，但缺点是必须在每个工作站上安装这些驱动程序。
However, if you are using newer date formats in SQL server, then you need to link the tables using the native 11 drivers, and you ALSO have to distribute and install the native 11 drivers on each workstation in addition to Access (or the Access runtime). So while one should prefer and use the newer SQL drivers, the downside is these drivers have to be installed on each workstation.
因此，请仔细检查这些列的数据类型， Access以表设计模式显示-如果它们是文本，则需要与本机11驱动程序重新链接。
So double check the data type for those columns that Access shows in table design mode – if they are text, then you need to re-link with the native 11 drivers.
这篇关于比较Access中SQL Server链接表中的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
