Error while converting XML date data to SQL column data.

Gani_tpt 1,666 Reputation points
2022-01-19T11:30:41.547+00:00

I am trying to insert XML date data to SQL table.

my XML field is "DateRelieved" and its XML element value is "2022-02-25T00:00:00.000Z"

My SQL Table column name is "DateRelieved" and its data type is DateTime.

while processing the code through .net getting below error.

'The given value of type String from the data source cannot be converted to type datetime of the specified target column.'

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,864 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,350 questions
{count} votes

Accepted answer
  1. Jack J Jun 24,296 Reputation points Microsoft Vendor
    2022-01-31T08:38:02.38+00:00

    @Gani_tpt , based on my test, I find that I can still convert your xml value to DateTime field by your code.

    I used the following database design:
    169802-image.png

    Also, I used the folllowing code:

            DataSet dataSet = new DataSet();  
            dataSet.ReadXml(@"C:\Users\username\Desktop\test1.xml");  
            string connstr = @"connstr";  
            SqlConnection connection = new SqlConnection(connstr);  
            connection.Open();  
            SqlBulkCopy copy = new SqlBulkCopy(connection);  
            copy.DestinationTableName = "Employee";  
            copy.ColumnMappings.Add("EmpRetired", "EmpRetired");  
            copy.ColumnMappings.Add("CurrentStatus", "CurrentStatus");  
            copy.ColumnMappings.Add("DateRelieved", "DateRelieved");  
            copy.ColumnMappings.Add("EmpId", "EmpId");  
            copy.ColumnMappings.Add("SearchRes_Id", "SearchRes_Id");  
            copy.WriteToServer(dataSet.Tables["Employee"]);  
    

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
  1. Karen Payne MVP 35,196 Reputation points
    2022-01-19T13:03:31.327+00:00

    Have you tried to convert, something like this?

    static void Main(string[] args)
    {
    
        XElement element = new("DateRelieved", "2022-02-25T00:00:00.000Z");
    
        Console.WriteLine(element);
        DateTime parsed = (DateTime)element;
        Console.WriteLine(parsed);
    }
    
    0 comments No comments

  2. Olaf Helper 41,006 Reputation points
    2022-01-24T11:16:05.413+00:00

    SqlBCopy1.ColumnMappings.Add(col1.ColumnName, col1.ColumnName);

    Why do you think there is just one table/+1 column.
    I see the relation SearchRes => Employee => 3 properties.