why is the following code giving error when converting from string to datetime

Question

0.00/5 (No votes)

See more:

, +

I have this following code which is saying 'cannot open SQL connection System.Data.SqlClient.SqlException (0x80131904): The conversion of a varchar data type to a datetime data type resulted in an out-of-
range value.' This is happening when the date '13-01-2013' is entered. It is taking 13 as month though I specify it as date. Code is as follows. Please advice.

C#

for (int i = 1; i < 13; i++)
{
   for (int j = 1; j < 31; j++)
   {
      for (int k = 0; k < 24; k++)
      {
         for(int l=0;l<60;l=l+10)
         {
            DateTime myDate = DateTime.ParseExact("2013-" + i.ToString("00") + "-" + j.ToString("00") + " "+k.ToString("00")+":" + l.ToString("00") + ":00", "yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture);
            string query = "INSERT INTO GCMemory(AgentID,GCTitle,GCProcessName,GCWindowHandle,GCMemory,DTime)" + "VALUES(1,'" + "Inbox (312) - mayooran99@gmail.com - Gmail - Google Chrome" + "','" + "chrome" + "','" + r.Next(1, 184467440).ToString() + "','" + r.Next(1, 1000000000).ToString() + "','" + myDate + "')";
            SqlCommand insertQuery = new SqlCommand(query, myConnection);
            insertQuery.ExecuteNonQuery();
         }
         Console.WriteLine("one day done");
      }
      Console.WriteLine("one month done");
   }
}

Posted 27-Aug-14 4:42am

mayooran99

Updated 27-Aug-14 4:52am

Nelek

v3

Add a Solution

Comments

Thanks7872 27-Aug-14 10:45am

So 13-01-2013 is string and you want it to be converted to DateTime,right?

mayooran99 27-Aug-14 10:57am

yes, please help. I need convert this to datetime to pass it to the DB

Nelek 27-Aug-14 10:53am

Might it be that you installed the DB / is running under english/american settings?
13.January.2013 is 01-13-2013
So Day and Month change places in the short format. That could be causing your crash

mayooran99 27-Aug-14 10:59am

how can I handle this issue? Please note that I don't have the sa password for the database. Are there any ways to change this setting?

Nelek 27-Aug-14 11:05am

To be sure that my supposing is correct, you can try to give the data 01-02-2013
If it is saved as 1st.February.2013 then I am wrong
If it is saved as 2nd.January.2013 without errors, then I think I am right

If I am right, you can just invert the order of the two parts of the string. It should be much easier than get ride of the settings in the database. You can not know what would you mess up if you change the database.

2 solutions

Solution 1

Simple solution: change your contatenation of the date to order by day, then month, then year.
For example convert(datetime, mydate, 103).

Posted 27-Aug-14 4:55am

Tim Carmichael

Comments

mayooran99 27-Aug-14 11:00am

Changing the concatenation to day,month, year also doesn't work. Any ideas? It is giving the same error

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2014-08-27T05:07:00

The first, and most obvious, problem is that you're using string concatenation to build your query. In this particular instance, since you're only dealing with known values that you've generated, it's not a huge problem. However, if you're using this pattern anywhere else, particularly where the parameters are provided by the user, it will be susceptible to SQL Injection[^].

Change your code to use a parameterized query. Not only will it fix the problem with passing a date parameter, but by getting out of the habit of using string concatenation to build your queries, you won't have a nasty surprise later on when Bobby Tables[^] uses your application. :)

The second problem is that your loop assumes 31 days for every month. As soon as you get to February, your DateTime.ParseExact call will fail.

Try something like this:

C#

using (SqlCommand insertQuery = new SqlCommand("INSERT INTO GCMemory(AgentID, GCTitle, GCProcessName, GCWindowHandle, GCMemory, DTime) VALUES (@AgentID, @GCTitle, @GCProcessName, @GCWindowHandle, @GCMemory, @DTime)", myConnection))
{
    insertQuery.Parameters.AddWithValue("@AgentID", 1);
    insertQuery.Parameters.AddWithValue("@GCTitle", "Inbox (312) - mayooran99@gmail.com - Gmail - Google Chrome");
    insertQuery.Parameters.AddWithValue("@GCProcessName", "chrome");
    
    var GCWindowHandle = insertQuery.Parameters.Add("@GCWindowHandle", SqlDbType.Int);
    var GCMemory = insertQuery.Parameters.Add("@GCMemory", SqlDbType.Int);
    var DTime = insertQuery.Parameters.Add("@DTime", SqlDbType.DateTime);

    DateTime theDate = new DateTime(2013, 1, 1);
    while (theDate.Year == 2013)
    {
        GCWindowHandle.Value = r.Next(1, 184467440);
        GCMemory.Value = r.Next(1, 1000000000);
        DTime.Value = theDate;
        insertQuery.ExecuteNonQuery();
        
        DateTime nextDate = theDate.AddMinutes(1);
        if (nextDate.Month != theDate.Month)
        {
            Console.WriteLine("one month done");
        }
        else if (nextDate.Day != theDate.Day)
        {
            Console.WriteLine("one day done");
        }
        
        theDate = nextDate;
    }
}