0

Most of my BizTalk integrations write data to SQL Server databases. I've got an XLST direct message coming in from another application, and it's mapping to a SQL Server stored procedure.

There are 4 dates that need to be mapped over, and I'm using a mapping functoid to convert them with the following:

public string StringToDatetime(string input)
{
    DateTime dt;

    if (DateTime.TryParse(input, out dt))
    {
        return dt.ToString("yyyy-MM-dd");
    }
    else
    {
        return "";
    }
}

I repeatedly receive the below error no matter what format I try to format dt as (including 8061 format)

Failed to convert parameter value from a String to a DateTime.

System.FormatException: String was not recognized as a valid DateTime.

Input value example;

<date_of_birth>1996-05-04T00:00:00Z</date_of_birth>

Can anyone please advise on the conventional/standard approach to formatting a string datetime for SQL Server consumption?

mrc85
  • 73
  • 10
  • 1
    This error is not produced by the DateTime.TryParse. Shoving in anything "non date" will likely result in the output being "0001-01-01". I think you need to look a little upstream for the location of the error. Have you tried using the SQL Profiler to see what is being send in? – Marvin Smit Apr 23 '23 at 06:51
  • I usually write a C# test and verify my functoids produce valid output for all inputs. I notice you return an empty string if it doesn't convert. Is that your issue? – Jay Jun 12 '23 at 18:46
  • I've figured out that it's when I'm not receiving a date to pass into the functoid, the elements missing so it therefore can't process the parameter. This is an issue I've been long plagued with, what a pain. – mrc85 Jun 16 '23 at 08:11

0 Answers0