Adding time to SQL date query

Question

var datadb1 = DateTime.ParseExact(dateTimePicker1.Text, "dd/MM/yyyy", null);
var timedb1 = DateTime.ParseExact(dateTimePicker2.Text, "HH:mm:ss", null);
var datadb2 = DateTime.ParseExact(dateTimePicker3.Text, "dd/MM/yyyy", null);
var timedb2 = DateTime.ParseExact(dateTimePicker4.Text, "HH:mm:ss", null);
commanddb.CommandText =
            "SELECT * FROM testtab WHERE datatime >= @from and datatime < @to";
commanddb.Parameters.AddWithValue("@from", datadb1);
commanddb.Parameters.AddWithValue("@to", datadb2);    

Need to add time check to this query (I`m getting time info from dateTimePicker2 and dateTimePicker4).

Solution

Need to add time check to this query

The problem with your code is that you are passing only the date part to check to the SQL query. In order to make your query check both the date and time parts, you have to:

• Declare the SQL parameters of data type datetime(SqlDbType.DateTime).
• The value you are passing to the sql parameter should be of data type DateTime and contains both parts the date and time parts.

One way to achieve this is by using the same DateTimePciker to pass both date and time parts, then don't use the datetimepicker Text property and use DateTimePicker.Value property instead, it will give you both date and time parts:

SqlParameter fromParam= new SqlParameter("@from", SqlDbType.DateTime);
fromParam.Value = dateTimePicker1.Value;

SqlParameter toParam= new SqlParameter("@to", SqlDbType.DateTime);
toParam.Value = dateTimePicker2.Value;

commanddb.Parameters.Add(fromParam);
commanddb.Parameters.Add(toParam);

---

Or, by adding both the date part and time part coming from different datetimepickers to the same DateTime variable before passing it to the sql parameter. Something like this:

var datadb1 = DateTime.Parse(dateTimePicker1.Value.ToShortDateString()); 
var timedb1 = DateTime.Parse(dateTimePicker2.Value.ToShortTimeString());

DateTime datetimeCombined1 = datadb1 + new TimeSpan(timedb1.Hour, 
                                                    timedb1.Minute,
                                                    timedb1.Second);

Then you have to pass this variable datetimeCombined1 to the SQL parameter, the same with the second datetime range, you have to combine both the parts before passing it.

This is assuming that you are using dateTimePicker1 to read the date part only and the dateTimePicker2 to read the time part only.

Comments

[Admin]

why don't you try sms backup and restore [by Ritesh sahu] from playstore. It export the file in xml format and also an xsl stylesheet to read the messages.

[Admin]

@Web-E: Thank you very much for your comment! I used it to back up my text messages and it worked~ Thank's! But how to save my contacts?

[Admin]

i use Easy Backup with dropbox, you should try that.

[Gordon Linoff]

That's nice. What do you mean "need to add time check to this query"? How about sample data and desired results.

[Alex Alex]

Its my first sql proj. U mean using not 4 dateTimePickers but 2?

[Jon Skeet]

Well, I suspect Gordon means "explain what you're trying to do more clearly".

[Admin]

I use SMS Backup+, which backs up call log and SMS to Gmail.