none
BizTalk 2010 - Convert a string to a datetime field in sql RRS feed

  • Question

  • Hi,

    I wonder if there is a way to convert the current date and time from the datetime functoid from string into datetime field and store it in the database as datetime?

    Thanks!

     

    Monday, July 25, 2011 2:46 PM

Answers

  • You can directly map the Date and Time functoid to the datetime type of element in the destination schema.

    If your schema has only Date/Time type element then you can use Date/Time functoid respectively. Read Date and Time Functoids Reference

    If you have a library function in .NET you can use the Script functoid and use Custom .NET assembly type along with the method. Read Scripting Using External Assemblies

    If you want to store datetime in a database you can use WCF SQL adapter for SQL database or WCF-Oracle adapter for Oracle. But this cannot be done in the map but in the orchestration.

    You can develop a custom functoid which can take parameter from Date Time Functoid as a parameter and insert the date time into the database. See Custom Functoid (BizTalk Server Sample) and Developing Custom Functoids.

    There is no other direct way to enter Date Time from Functoid to the database.


    Abdul Rafay - MVP & MCTS BizTalk Server
    blog: http://abdulrafaysbiztalk.wordpress.com/
    Please indicate "Mark as Answer" if this post has answered the question.
    • Proposed as answer by Rohit.SharmaModerator Tuesday, July 26, 2011 4:10 AM
    • Unproposed as answer by FrankTo Tuesday, July 26, 2011 11:29 PM
    • Marked as answer by FrankTo Friday, March 14, 2014 1:00 PM
    Monday, July 25, 2011 3:40 PM
  • Frank Sorry I missed out that the XML Format of DateTime is YYYY-MM-DDTHH:MM:SS while in SQL Server its the same as in .NET. To wrap this up if you need the current date time use a scripting functoid and with Custom C# code return DateTime.Now and map it to the SQL Server DateTime element.

    You can use the DateTime.Parse() method to convert from XML (ISO 8601) format to SQL datetime Format.

    If you have the same XML format as an input in the request and you want to convert it to the datetime format I would say to use an External Assembly helper method within the Map. You can use the script functoid and C# code but writing this code once into a class Library and then calling it from all of your projects would be a good idea for maintenance and updates.

    You can also use XSLT to convert date time formats there is an XSLT Libraray for DateTime Formats.

     


    Abdul Rafay - MVP & MCTS BizTalk Server
    blog: http://abdulrafaysbiztalk.wordpress.com/
    Please indicate "Mark as Answer" if this post has answered the question.
    • Marked as answer by FrankTo Tuesday, July 26, 2011 11:29 PM
    Tuesday, July 26, 2011 9:02 PM

All replies

  • You could always use a scripting functoid and pass in a string and convert it to DateTime.
    Ankit
    Monday, July 25, 2011 3:32 PM
  • You can directly map the Date and Time functoid to the datetime type of element in the destination schema.

    If your schema has only Date/Time type element then you can use Date/Time functoid respectively. Read Date and Time Functoids Reference

    If you have a library function in .NET you can use the Script functoid and use Custom .NET assembly type along with the method. Read Scripting Using External Assemblies

    If you want to store datetime in a database you can use WCF SQL adapter for SQL database or WCF-Oracle adapter for Oracle. But this cannot be done in the map but in the orchestration.

    You can develop a custom functoid which can take parameter from Date Time Functoid as a parameter and insert the date time into the database. See Custom Functoid (BizTalk Server Sample) and Developing Custom Functoids.

    There is no other direct way to enter Date Time from Functoid to the database.


    Abdul Rafay - MVP & MCTS BizTalk Server
    blog: http://abdulrafaysbiztalk.wordpress.com/
    Please indicate "Mark as Answer" if this post has answered the question.
    • Proposed as answer by Rohit.SharmaModerator Tuesday, July 26, 2011 4:10 AM
    • Unproposed as answer by FrankTo Tuesday, July 26, 2011 11:29 PM
    • Marked as answer by FrankTo Friday, March 14, 2014 1:00 PM
    Monday, July 25, 2011 3:40 PM
  • Hi,

    For some options with datetime and functoids see this post. After setting the right format of datetime map it to insert schema. You can insert into SQL Server database based on guidance on my post (I think you already seen it) on table operations.

    HTH

    Regards,

    Steef-Jan Wiggers
    MVP & MCTS BizTalk Server 2010
    http://soa-thoughts.blogspot.com/
    If this answers your question please mark it accordingly


    BizTalk
    Tuesday, July 26, 2011 8:58 AM
    Moderator
  • Hi Abdul,

    Thank you for the suggestions. They are really helpful. Ideally, I would like to use the datetime functoid to get away with the custom scripting. The source does not have the datetime field but the field in the destination is datetime field to hold the currentdate and time of the transaction. Unfortunately, the functoid produces a string, and my question is can we use a trick of some sort to convert it back to datetime and store it in the database. I used the currentdate functoid but BizTalk complained that the format was incorrect, which it is understandable since it was a string not datetime. Maybe we have to use xpath or xlate or some function to do the job? I am very new to this so sorry to post a maybe obvious question.

    Tuesday, July 26, 2011 5:58 PM
  • Yep, Steef. I think I understand your post regarding inserting a field in SQL. But this is just a simple field to hold currentdate of the transaction. Maybe it is an overkill to use SQL adapter? Of course, if this is the only way to add a sql datetime field, then I would have to use it. If I can convert a functoid into datetime field, it would be even better. Hope what I am talking makes sense to you as I am a green horn in this.
    Tuesday, July 26, 2011 6:02 PM
  • Thanks Ankit. That is what I am thinking of, but how to convert it into datetime without involving sql adapter?
    Tuesday, July 26, 2011 6:03 PM
  • Frank Sorry I missed out that the XML Format of DateTime is YYYY-MM-DDTHH:MM:SS while in SQL Server its the same as in .NET. To wrap this up if you need the current date time use a scripting functoid and with Custom C# code return DateTime.Now and map it to the SQL Server DateTime element.

    You can use the DateTime.Parse() method to convert from XML (ISO 8601) format to SQL datetime Format.

    If you have the same XML format as an input in the request and you want to convert it to the datetime format I would say to use an External Assembly helper method within the Map. You can use the script functoid and C# code but writing this code once into a class Library and then calling it from all of your projects would be a good idea for maintenance and updates.

    You can also use XSLT to convert date time formats there is an XSLT Libraray for DateTime Formats.

     


    Abdul Rafay - MVP & MCTS BizTalk Server
    blog: http://abdulrafaysbiztalk.wordpress.com/
    Please indicate "Mark as Answer" if this post has answered the question.
    • Marked as answer by FrankTo Tuesday, July 26, 2011 11:29 PM
    Tuesday, July 26, 2011 9:02 PM
  • Thank you, Abdul for your direction. I think that is what I am looking for. I will give it a try. So basically, I have 2 options:

    1) use a scripting functoid which is pretty straight forward and easy enough for me... :)

    or

    2) use the datetime functoid with xslt to map it to the datetime field in the database? I am new to this, I apologize if I misunderstand your explanation.

    Thanks again!

    Tuesday, July 26, 2011 11:29 PM