Excel: Query SQL table with date parameter from cell

Question

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

 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

I have the parameter set to read from cell (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.

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.

Jay · Accepted Answer · 2015-04-07 18:09:35Z

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

answered Apr 7, 2015 at 18:09

Jay

4755 gold badges18 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Saagar Elias Jacky · Accepted Answer · 2015-04-07 15:30:37Z

0

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#

answered Apr 7, 2015 at 15:30

Saagar Elias Jacky

2,6842 gold badges17 silver badges28 bronze badges

3 Comments

Jay Over a year ago

Thanks for the response but where shoudl the # #s go? If I put them in my command text (SELECT * FROM TABLE WHERE myDate= #?# I get a slew of errors (invalid column name '#@P1#' , Statement(s) could not be prepared / The following data range failed to refresh" etc...

Jay Over a year ago

Hi Saagar I have no VBA code. I tried putting a form button in to worksheets.refreshall at one point but that didnt work so I got rid of it. Its all being done through the connection definition tabs

Saagar Elias Jacky Over a year ago

check this out stackoverflow.com/questions/24907360/…

Tayyab Amin · Accepted Answer · 2016-03-22 19:13:41Z

0

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

Try this:

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

Thanks

answered Mar 22, 2016 at 19:13

Tayyab Amin

1391 gold badge2 silver badges8 bronze badges

Collectives™ on Stack Overflow

Excel: Query SQL table with date parameter from cell

3 Answers 3

Comments

3 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Linked

Related