sql-serverazure-sql-databaseazure-logic-apps

Logic App to call Azure SQL stored procedure passing datetime param

I have a simple stored procedure in an Azure SQL database that takes a single param of type datetime:

CREATE PROCEDURE [dbo].[DeleteLogRecs]
    (@pSinceBefore datetime)

I'd like to call this from an Azure Logic App passing a date value of Now -3 days, so I have the following action:

"Execute_stored_procedure_(V2)": {
                "inputs": {
                    "body": {
                        "pSinceBefore": "@{convertFromUtc(addDays(utcNow(),-3),'GMT Standard Time')}"
                    },
                    "host": {
                        "connection": {
                            "name": "@parameters('$connections')['sqldw']['connectionId']"
                        }
                    },

My problem is, I get the following error:

"status": 400,
"message": "Error converting data type nvarchar to datetime.\r\nclientRequestId: dbbd2502-899f-4812-a942-4c11bbed4c1b",
"error": {
"message": "Error converting data type nvarchar to datetime."

enter image description here

Can anyone see where I'm going wrong?

Solution

  • I've managed to bodge this by changing the type of the stored procedure parameter to a string and using the following formatting from the logic app:

    "Execute_stored_procedure_(V2)": {
                "inputs": {
                    "body": {
                        "pSinceBefore": "@{formatDateTime(addDays(utcNow(),-3),'yyyy-MM-dd HH:mm:ss')}"
                    },