1

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.

Community
  • 1
  • 1
Jay
  • 455
  • 3
  • 17
  • 34

3 Answers3

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

Jay
  • 455
  • 3
  • 17
  • 34
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#
Saagar Elias Jacky
  • 2,684
  • 2
  • 14
  • 28
  • 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 Apr 07 '15 at 15:39
  • 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 – Jay Apr 07 '15 at 15:42
  • check this out http://stackoverflow.com/questions/24907360/conversion-failed-when-converting-date-and-or-time-from-character-string-in-sql – Saagar Elias Jacky Apr 07 '15 at 15:46
0

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

Try this: 

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

Thanks

Tayyab Amin
  • 129
  • 1
  • 2
  • 8