Excel:使用单元格中的日期参数查询SQL表

I have an ODBC connection, which simply queries a SQL table:

我有一个ODBC连接，它只查询SQL表:

 SELECT * FROM [TABLE] WHERE myDate = ?

If I run the query in MS Query, it prompts for a date and I can just enter 4/2/2015 and it returns 4/2/2015 data perfectly

如果我在MS查询中运行查询，它会提示一个日期，我只需输入4/2/2015，它会完美地返回4/2/2015数据

I have the parameter set to read from cell (B1):

我有从单元格(B1)读取的参数设置:

=WorkSheetName!$B$1

When I switch back to Excel and put 4/2/2015 in B1 and then refresh - it gives me a conversion failed when converting date and/or time from character string error.

当我切换回Excel，在B1中输入4/2/2015，然后刷新——它会在将日期和/或时间转换为字符串错误时导致转换失败。

I tried editing my query to WHERE CONVERT(Varchar(10),myDate,101) = ? but had no luck. Not sure why I am getting this, seems like it should be so simple.

我尝试将查询编辑到CONVERT(Varchar(10)，myDate,101) = ?但没有运气。不知道我为什么会得到这个，看起来应该很简单。

3 个解决方案

#1

I appreciate the feedback I was getting - but it turned out to be something very simple on my part that I was overlooking. The actual cell I was keeping my date was formatted as a date, and giving a conversion error. Once I formatted it to a text cell, it returned the data properly for the given date. Thanks

我很感激我得到的反馈——但我发现我的反馈非常简单，我忽略了它。我保存日期的实际单元格被格式化为日期，并产生转换错误。一旦我将它格式化为一个文本单元，它就会正确地返回给定日期的数据。谢谢

#2

The filter part must be padded with # while trying to use Date as a filter.

在尝试使用日期作为过滤器时，必须用#填充过滤部分。

It should be like

它应该像

SELECT * FROM [TABLE] WHERE myDate = #4/2/2015#

#3

Converting the "myDate" column to "Smalldatetime" format should work for you.

将“myDate”列转换为“Smalldatetime”格式应该适用于您。

Try this:

试试这个:

SELECT * FROM [TABLE] WHERE Cast(myDate as smalldatetime) = ?

Thanks

谢谢

#1

#2