Error with Dynamic SqlCommand Query with DateTime type

System Information (please complete the following information):

• Model Builder Version (available in Manage Extensions dialog): 16.6.3.2136603
• Visual Studio Version VS 2019 ~ 16.11.2

Describe the bug

• On which step of the process did you run into an issue: Train Model
• Clear description of the problem: a SqlCommand query fails when there is a space character in a column name. Here is the query that is failing:
  
  

  SELECT Eastern_Daylight_Time_240,
  
  CAST(Memory_Available_MBytes as REAL),
  
  CAST(Processor_Total_Pct_Processor_Time as REAL),
  
  CAST(System_Processor_Queue_Length as REAL),
  
  CAST(Buffer_Manager_Buffer_cache_hit_ratio as REAL),
  
  721 Crossdock Error Fix,
  
  [<other columns>]
  
  FROM [dbo].[PerfmonLogsSubset]

Screenshots
Here is an image of the error:

[beccamc]

Thanks for reporting @BeginTry! I assume you manually edited the query string in the .mbconfig file?

@beccamc: Hmmm. The query is in the .mbconfig file? I didn't know that. :)

I ended up creating a VIEW on the TABLE using aliases that didn't require QUOTENAME.

I've tried some additional testing with 16.6.3.2136603 and now I am getting a new error (see below). The SQL table I am working with has about 50k rows and is only about 10MB. I could dump that to a separate database and provide you with a native SQL Server backup. I'm happy to do that if desired.

Follow Up:
The issue appears to be the SQL Server DATETIME data type. I tried editing the query in the .mbconfig file. Casting the DATETIME column to SMALLDATETIME did not work. However, returning the data as VARCHAR did work. Specifically, I used the CONVERT function:

SELECT 
	CONVERT(VARCHAR, [Eastern_Daylight_Time_240], 121),
	[Column_2],
	[Column_3],
	...
	[Column_n]
FROM [dbo].[PerfmonLogsSubset]

[beccamc]

Thanks for the follow up. Sounds like you've unblocked yourself?

We've had problems converting SQL to types that ML.NET is happy with. I'll add this issue to the SQL improvement list for investigation.

[beccamc]

Two things going on here...

1. We should surface the SQL command and allow users to manually edit if they wish. Custom SQL Queries #1935
2. We should add DateTime support for both SQL and text types. It's available in DbType and in DataKind. IIRC this means we can add support directly.