Conversion failed when converting datetime from character string

[piticcotoc]

I have a database (mssql 2005) with a table which has a datetime column. I'm trying to insert data into tha table from c# but i'm getting this exception: "Conversion failed when converting datetime from character string". Here is the code (stripped of connection declaration).

SqlParameter currentDate = new SqlParameter();
currentDate.Value = System.DateTime.Now;
currentDate.ParameterName = "@dataTrimitereService";
string insert = "INSERT INTO table(col 1, col 2, date) VALUES ('value 1', 'value 2', '@dataTrimitereService');
sqlCommand.ExecuteNonQuert(insert);

I've also tried changing regional settings and date format but no success. Thanks.

[Niladri_Biswas]

What is value in @dataTrimitereService?

Niladri Biswas

modified on Sunday, June 28, 2009 3:59 AM

[piticcotoc]

it's System.DateTime.Now which returns 28.06.2009 10:30:30 (example).

[Niladri_Biswas]

Hi, I have created a similar kind of thing like yours. A table with 3 fileds (COL1,COL2,Date) as (VARCHAR(50),VARCHAR(50),DATETIME respectively)

In C# class, I have written this statement

public class DBConnectionSQL
{
string connectionPath = "Data Source=Niladri135\\SQLEXPRESS;Initial Catalog=test;Integrated Security=True";
string command = "";

    public void InsertRecords()
    {

        SqlConnection connection = new SqlConnection(@connectionPath);
        DateTime currentValue  = System.DateTime.Now;
        command = "Insert Into test\_Datetime (COL1, COL2,DATE) values('value 1', 'value 2'," + "'" +  currentValue + "'" +  ")";
        try
        {
            connection.Open();
            SqlCommand cmd = new SqlCommand(command, connection);
            cmd.ExecuteNonQuery();
        }
        catch (SqlException sqlExcep)
        {
            string msg = sqlExcep.Message;
        }
        finally
        {
            connection.Close();
        }
    }
}

And I got the correct result. N.B.~ Check the value in the insert string in your case. Is the datetime value within single quote e.g.'6/28/2009 1:20:55 PM' ? Hope this helps :)

Niladri Biswas

[piticcotoc]

Passing the date to the select statement directly (not parameter) raises this exception: "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.\r\nThe statement has been terminated."

[piticcotoc]

I've ran the query directly in sql and tried different date formats. It finnaly accepted "mm/dd/yyyy hh:mm:ss". How can I make c# convert into the right format without changing regional settings. My current date format (from regional settings) is dd.mm.yyyy

[Niladri_Biswas]

Hi, Last day there was some problem in the afternoon in posting the message. So I was unable to reply you though I tried many times. However, I emailed you the solution. Try with the solution which I gave . That's a running application. N.B.~ The default DateTime format in my system is "mm/dd/yyyy hh:mm:ss" By the way if you need to convert the date format e.g. dd.mm.yyyy to mm/dd/yyyy hh:mm:ss, use the**

DateTime.ParseExact() Method

**.

e.g. I want to convert from 28.06.2009 i.e. dd.mm.yyyy to

mm/dd/yyyy hh:mm:ss

string OldDateFromat = "28.06.2009";//dd.mm.yyyy
DateTime dt = DateTime.ParseExact(OldDateFromat, "dd'.'MM'.'yyyy",
CultureInfo.InvariantCulture);

Hope this helps you. Let me know in case of any concern :)

Niladri Biswas

modified on Sunday, June 28, 2009 11:25 PM

[Luc Pattyn]

Niladri_Biswas wrote:

The default date format in c sharps Datetime.Now is "mm/dd/yyyy hh:mm:ss".

I disagree: the default DateTime format is the one specified in your Regional Settings Control Panel, it is up to the user to choose the format he likes to use. FYI: For data interchange, there is an IEC 8601 standard, as explained here[^]. FWIW: where ever possible, a real date or datetime field is better than a string. :)

Luc Pattyn [Forum Guidelines] [My Articles]

---

DISCLAIMER: this message may have been modified by others; it may no longer reflect what I intended, and may contain bad advice; use at your own risk and with extreme care.

---

[Niladri_Biswas]

Correct . I am sorry for my mistake . I edited that line. :)

Niladri Biswas

[piticcotoc]

Thanks for your help. I've done it with DateTime.Parse() already.