Invalid SELECT Sql Statement on External Indicator

[SBratcher]

I am trying to create a simple external indicator to add a count to a dashboard in PA, but when I Test the Collection I am receiving an "Invalid SELECT Sql Statement" error. 

I have tried every version I can think of to get the SQL to work including things like:

SELECT COUNT(*) AS value FROM [database].[schema].[table] WHERE DateField= ${start_date}

SELECT COUNT(*) AS value FROM [schema].[table] WHERE DATE(DateField) = DATE(${start_date})

I have also tried setting an exact date instead of using the variable but that has not worked either. 

When I run SELECT COUNT(*) AS value FROM [database].[schema].[table] WHERE DateField= GetDate() in SSMS it runs perfectly, I have also set up a new data source that runs this query and it test loads the data fine, so it isn't a connection issue. There are no breakdowns associated with the indicator, most of the fields were left as the defaults. 

Does anyone have any idea why this would be erroring?

[Saiganeshraja]

You are assigning text values to datetime variables

[SBratcher]

What do you mean? ${start_date} is the only variable that is in use and I am not assigning anything to it, just trying to use it to set the date. 

[SBratcher · Accepted solution]

I was able to get an answer from support.

It turns out that the examples and documentation are all formatted for MySQL, the proper MS SQL formatting is:

SELECT COUNT(*) AS value  FROM [database].[schema].[table] WHEREtry_convert(date,datefield) = try_convert(date,'${start_date}')