Forum Discussion

Shiva799's avatar
Shiva799
Frequent Visitor
8 years ago

Error converting data type varchar to date.

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

2 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    8 years ago

    Hi Shiva799,

     

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

    let 
    
bDate = BusinessDate

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

in Source

     

    Regards

    • Shiva799's avatar
      Shiva799
      Frequent Visitor
      8 years ago
      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