CODEWITHSUNDEEP
sqlexcelstored-procedurespowerquery
Igor Meszaros
Igor Meszaros

Reputation: 2127

Power Query M string to datetime conversion

I'm trying to invoke a stored proc with a nullable datetime value, but I'm running in circles with this. Below you can see all the combinations I've tried and the related errors:

FromDate = Excel.CurrentWorkbook(){[Name="FromDate"]}[Content]{0}[Column1],
FromDateQuery = if FromDate = "" then "" else "@FromDate = '"& DateTime.From(FromDate)&"',",

Expression.Error: We cannot apply operator & to types Text and DateTime.
Details:
    Operator=&
    Left=@FromDate = '
    Right=15/03/2017 00:00:00

If I follow the instructions and remove the & as suggested I get the following: Expression.SyntaxError: Token Comma expected.

Tried passing it through as a string:

FromDate = Excel.CurrentWorkbook(){[Name="FromDate"]}[Content]{0}[Column1],
FromDateQuery = if FromDate = "" then "" else "@FromDate = '"& Text.From(FromDate) &"',",

Message=Error converting data type varchar to datetime.

Query:

[Query="EXECUTE dbo.bsp_GetReport
"& UserQuery &"
"& FromDateQuery &"]

Also tried using Cast and Convert all in vain...

Upvotes: 1

Views: 1832

Answers (1)

MarcelBeug
MarcelBeug

Reputation: 2997

You can use DateTime.ToText with the required formatting, e.g.

FromDateQuery = if FromDate = "" then "" else "@FromDate = '"& DateTime.ToText(DateTime.From(FromDate), "dd/MM/yyyy HH:mm:ss")&"',",

Upvotes: 2

Related Questions