SQL Server DateTime and C # DateTime

Question

On my SQL server, I have a very simple testing table that contains only three rows: ID , Date and Hours . ( varchar , DateTime , varchar ).

SQL's DateTime format is similar: yyyy-MM-dd HH:mm:ss.fff .

The C # DateTime format is as follows: yyyy-dd-MM HH:mm:ss.fff .

I use the following code to get the C # DateTime format:

  string ddd = "2012-10-10 00:00:00.000"; dt1 = DateTime.ParseExact(ddd, "yyyy-MM-dd HH:mm:ss.fff", null); 

If I try to do this in yyyy-dd-MM , I get an error message:

DateTime, which is represented by a string, is not supported by calendaring.

In my C # application, I am trying to find the total hours between sunny dates. I kind of got it for work, but it only works for dates between 01 and 12.

So, from 2012-01-10 to 2012-10-10 (ten days) will give me the correct number of hours in the database.

But when I write 2012-01-10 to 2012-14-10 (fourteen days), I get an error message:

Convert char data type to out of range date and time data type

Thanks in advance.

PS. Can you suggest an easier way to get dates?

mySQL query

 string CommandText = "SELECT * FROM date_test WHERE id = '4' AND date BETWEEN '" + dt1 + "' AND '" + dt2 + "'"; 

I figured out a problem, but not a solution.

The problem is that the SQL database is looking for the format and wants yyyy-MM-dd, but C # can only send yyyy-dd-MM.

Why can't ParseExact() do yyyy-MM-dd?

Answer 1

DateTime, which is represented by a string, is not supported by the calendar.

This error is indicated because your C # application views the date 2012-14-10 as the month of the 14th , 10th and 2012th year . Find the day and year of work, but not a month. Also, do not try to change how your C # application views the date based on the system culture.

You are confused about how to define a DateTime object and how to display it.

Since you save the date as a DateTime in SQL, I have no good reason to believe that you will need to parse any . Consider the following code example.

 var dataTable = new DataTable(); var dataAdapter = new SqlDataAdapter("SELECT * FROM YourTable", "{connection string}"); dataAdapter.Fill(dataTable); var yourDate = dataTable.Rows[0]["Date"]; <=== the type will be DateTime, simple. 

Adding Options

Take your example query:

"SELECT * FROM date_test WHERE id = '4' AND date BETWEEN '" + dt1 + "' AND '" + dt2 + "'";

And let's fix it a bit, consider the example below:

 var dataTable = new DataTable(); var dataAdapter = new SqlDataAdapter("SELECT * FROM date_test WHERE id = @ID AND date BETWEEN @StartDate AND @EndDate", "{connection string}"); dataAdapter.SelectCommand.Parameters.AddWithValue("@ID", "4"); dataAdapter.SelectCommand.Parameters.AddWithValue("@StartDate", new DateTime(2012, 10, 1)); dataAdapter.SelectCommand.Parameters.AddWithValue("@EndDate", new DateTime(2012, 10, 14)); dataAdapter.Fill(dataTable); var yourDate = dataTable.Rows[0]["Date"]; <=== the type will be DateTime, simple. 

Answer 2

You are preparing the perfect foundation for SQL injection .
Also look here . Here is an example of a parameterized query.