5

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?

Improve this question
5
  • Why is this tagged with mysql tag? you are talking about sql server all over your question, but in the end there is a mysql query. Commented Sep 24, 2012 at 11:12
  • 2
    You are confusing the value of a DateTime with how it is presented (formatted). The value (in a DateTime or in a database) does not have a format as such (just an internal representation). Commented Sep 24, 2012 at 11:15
  • 1
    Use parameterised queries with parameters of correct datatypes and problem solved. Commented Sep 24, 2012 at 11:16
  • example please Martin :D Commented Sep 24, 2012 at 11:16
  • @Pavenhimself, take a look at my answer, I believe it will give you a code snippet to query the database like you need. Commented Sep 24, 2012 at 11:37

2 Answers 2

Reset to default
4

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

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

This program is only for my use only. No inputs from the dangerous internet :)
Then it's a great time to start forming good habits and practices. Dennis is correct; that's a perfect setup for SQL injection.
@Pavenhimself, I've got to agree with Dennis here, you should always practice just like you want to play.
3

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.
Improve this answer

4 Comments

Thanks alot. Finally got it to work :) All the formatting really did the job on me.
@Michael I have an issue when passing C# DateTime into SQL Server SqlDBType.DateTime. C# value = 31/07/2014 00:00:00 AM and this returns an exception String was not recognized as a valid DateTime.. Expected sql server value format = 2014-07-31 Hh:mm:ss.fff What can I do to fix this issue?
@bonCodigo convert that string to a DateTime before assigning it to the parameter; like this Convert.ToDateTime(input);.
@MichaelPerrenoud infact I have done it before came to your post. And mine works - it was a just a matter of not getting the updates from the server... :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.