CODEWITHSUNDEEP
c#sql
Pavenhimself
Pavenhimself

Reputation: 567

SQL server DateTime and C# DateTime

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?

Upvotes: 5

Views: 21929

Answers (2)

Mike Perrenoud
Mike Perrenoud

Reputation: 67918

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

This error is being given because your C# application views the date 2012-14-10 as saying the 14th month, 10th day, and 2012th year. The day and year work find, but the month doesn't. Further, don't try and change how your C# application views the date, that's based off the culture of the system.

You're confusing how to define a DateTime object and how to display one.

Since you're storing your date as a DateTime in SQL, there's not a good reason for me to believe that you would need to do any kind of parsing. Consider the below code sample.

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 Parameters

Let's 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 below example:

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.

Upvotes: 3

Dennis
Dennis

Reputation: 37780

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

Upvotes: 4

Related Questions