SQL server DateTime and C# DateTime

Question

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

The DateTime format in SQL is like: yyyy-MM-dd HH:mm:ss.fff.

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

I use the following code to get 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 make it into yyyy-dd-MM I get an error:

The DateTime, which is represented by the string, isn't supported by the calender.

In my C# application is am trying to find total amount of hours between sunder dates. I sort of got it to working, but it only works for dates between 01 and 12.

So like from 2012-01-10 to 2012-10-10 (ten days) will give me the correct amount of total hours from the database.

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

The conversion of a char data type to a datetime data type resulted in an out-of-range"

Thank you in advance.

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

mySQL query

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

I have figured out the problem but not the solution.

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

Why cannot ParseExact() do yyyy-MM-dd?

Answer 1

You're preparing a perfect ground for SQL injections.
Also look here. There's an example of parametrized query.