Search code examples
sqlexcelodbcremedy

Excel timestamp as parameter in MS Query results in Conversion Error

If found this thread, but unfortunately it didn't help:

Excel date parameter in SQL query

I tried the solution in the thread above and I get the following results:

Using the ODBC connection I have to use the below in order for it to accept the query:

HPD_Help_Desk.Submit_Date > { ts '2016-08-01 00:00:00' }

I've replaced this to the below, cause there is no other way (that I've found) it takes the parameter 

HPD_Help_Desk.Submit_Date > ?

My Original Cell value is:

7/31/2016

With the formula I tried several formats

=LOWER(TEXT(C2,"YYYY-MM-DD"))
=LOWER(TEXT(C2,"dd-mmmm-yyyy"))

when I try the solution above I get the error. I get the same error if I just point straight to the date value in Excel: 

[AR System ODBC Driver]Conversion Error

if I try this formula

=LOWER(TEXT(C2,"yyyy-mm-dd HH:mm:ss"))

I get the exact timestamp string '2016-07-01 00:00:00' but then I get:

[AR System ODBC Driver]Unrecognised Data Format

Any ideas if it's a limitation, or I'm just doing something wrong?

Solution

  • Ok, so after almost tearing all my hair out, I've found the solution.

    The expected format from the [AR System ODBC Driver] we use is:

    YYYY-MM-DD HH:mm:SS.FF

    for example:

    '2016-08-01 00:00:00.00'

    And yes, the [.] dot before the milliseconds is not a typo...

    I hope it will help somebody else struggling with this.