ADF - Lookup vs Stored Procedure Activity Date parameter handling (bug?)

Question

Hi All,

I have a lookup activity that gets a max date from an Azure SQL Table source.
The output looks like this: 2020-06-11T23:24:18.677

I have another stored procedure that logs that value in a separate table.
When I pass that date to a stored procedure nvarchar parameter I get two different values depending on where I use a lookup activity or a stored procedure activity.

If I pass through a lookup activity, I get this inserted:
6/11/2020 11:24:18 PM

If I call the same stored procedure through a stored procedure activity this is what is inserted:
2020-06-11T23:24:18.677

This is the value I need inserted, since the fractional seconds are important.
Fortunately, I can use the stored procedure activity in this instance, but there may be times where a lookup is necessary. It took me quite a while to figure out though.

Is this a bug or am I missing something?

Thanks.

Answer 1

Hi @JohnAherne-0132,

To achieve your desired intent, you can do the following :

• Use a lookup activity to query your dataset to get the max date value you want.
• Chain a Stored Procedure activity to your Lookup Activity.
• Under the settings of the Stored Procedure activity, under the Stored procedure parameters section, add your parameter and send the value from the output after parsing it using the formatDateTime function. The default format for the timestamp is "o" (yyyy-MM-ddTHH:mm:ss:fffffffK), which complies with ISO 8601 and preserves time zone information.

You can specify any desired output format for date time. You can additionally specify the format as : formatDateTime('<timestamp>', '<format>'?)

Please refer to the below screenshot :

Hope this helps.

Answer 2

Bit late to the party, but got bitten by this as well today. Very annoying issue.

Speaking to my colleagues, we think this might be to do with the Newtonsoft.Json library being used behind the scenes to bridge the gap between ADF and SQL Server, whereby Newtonsoft sees a string that looks like a datetime, and automatically converts it to a default datetime culture of m/d/yyyy hh:mi:ss PM before passing the argument to the sproc. This is the default behaviour for Newtonsoft, which it seems the ADF team might be using.

Only way around it (that I've found) is to convert the parameter back to the desired format once you're within the sproc. E.g.

CREATE PROCEDURE MySproc (
        @MyDateTime varchar(30))
AS BEGIN
        DECLARE @MyDatetimeAsDatetimeType DATETIME2 = CAST(@MyDateTime AS DATETIME2)
        SET @MyDateTime = CONVERT(varchar(30), @MyDatetimeAsDatetimeType , 126)                          --ISO8601 format

        ...

Ed