Type mismatch in expression 'System.Data.UnaryNode'

Question

I have a data table with a column (strDate) that contains values like '20150519'. I want to create a new DateTime column in the data table (without using a loop) that contains the date representation of strDate.

I tried this:

table.Columns.Add("NewDate", typeof(DateTime)).Expression = 
            "CONVERT((SUBSTRING(strDate, 0, 5) + '/' + 
                      SUBSTRING(strDate, 5, 2) +  + '/' + 
                      SUBSTRING(strDate, 7, 2)), 'System.DateTime')";

But it gives an error that: Type mismatch in expression 'System.Data.UnaryNode'

An underlying question here is: what Substring function is being used here? SQL or .Net? From the documentation I understand that it is not SQL. But .Net's Substring is a unary operator (which is inline with the error message), but then I would think my statement should look like:

table.Columns.Add("NewDate", typeof(DateTime)).Expression = 
           "CONVERT((strDate).Substring(0, 4) + '/' + 
                    (strDate).Substring(4, 2) + '/' + 
                    (strDate).Substring(6, 2), 'System.DateTime')";

which in turns produce the error:

"Cannot interpret token '.' at position 21."

Answer 1

This solution eventually worked for me. Note that Substring is not 0 based, thus I would assume it is the SQL version of Substring.

table.Columns.Add("ChangeDate", typeof(DateTime)).Expression = 
                  "CONVERT(SUBSTRING([APO_CHDATE], 1, 4) + '/' + 
                           SUBSTRING([APO_CHDATE], 5, 2) + '/' + 
                           SUBSTRING([APO_CHDATE], 7, 2), 'System.DateTime')";