Error converting data type varchar to date.

[Shiva799]

Hi, I'm trying to write an m code for an sp to take parameters dynamically. It goes as let Source = Sql.Database("servername", "dbname", [Query="exec [SP_GetReport] 'Status','All','Frequency','BusinessDate'"]) in Source But it gives this error DataSource.Error: Microsoft SQL: Error converting data type varchar to date. Details: DataSourceKind=SQL DataSourcePath=blccnamist;MI_FLATTENED_VIEW Message=Error converting data type varchar to date. Number=8114 Class=16 Also 'All' is not a parameter but value and date format accepted by sp is yyyy-mm-dd i also tried formatting the business date parameter to Date.FromText(Text.From([BusinessDate])) or Date.FromText(Text.From(['BusinessDate'])) or Date.FromText(Text.From(['&BusinessDate&'])) or "Date.FromText(Text.From([BusinessDate]))" or '&Date.FromText(Text.From([BusinessDate]))&' but its still not working I hope someone might be able to tell me where exactly I'm going wrong Thanks in advance

[v-ljerr-msft]

Hi @Shiva799,

Could you try using the query below to see if it works? 

let 

bDate = BusinessDate

Source = Sql.Database("servername", "dbname", [Query="exec [SP_GetReport] 'Status','All','Frequency','"&bDate&"'"]) 

in Source

Regards

[Shiva799]

Nope Expression.Error: We cannot apply operator & to types Text and Date. Details: Operator=& Left=exec [SP_ReportModel_Policy_Summary_Base_Template_AA_RA_Parm] 'Status''Frequency',' Right=12/7/2017 Also it gave me error without comma let bDate = BusinessDate, Source = Sql.Database("servername", "dbname", [Query="exec [SP_GetReport] 'Status','All','Frequency','"&bDate&"'"]) in Source