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).

That's nice. What do you mean "need to add time check to this query"? How about sample data and desired results. — Gordon Linoff
– Gordon Linoff, Commented Oct 11, 2015 at 13:17
Its my first sql proj. U mean using not 4 dateTimePickers but 2? — Alex Alex
– Alex Alex, Commented Oct 11, 2015 at 13:19
Well, I suspect Gordon means "explain what you're trying to do more clearly". — Jon Skeet
– Jon Skeet, Commented Oct 11, 2015 at 13:23

Mahmoud Gamal · Accepted Answer · 2015-10-11 14:03:41Z

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.

Mohsen Ebnetorab · Accepted Answer · 2015-10-11 14:19:14Z

If you want to use each dateTimePicker for Date or Time seperatly you can define a DateTime variables and Set its Date value and Time Value like this:

    DateTimePicker dateTimePickerFromDate = new DateTimePicker();
    DateTimePicker dateTimePickerFromTime = new DateTimePicker();
    DateTimePicker dateTimePickerToDate = new DateTimePicker();
    DateTimePicker dateTimePickerToTime = new DateTimePicker();

    DateTime fromDateTime = new DateTime(dateTimePickerFromDate.Value.Year, 
        dateTimePickerFromDate.Value.Month, dateTimePickerFromDate.Value.Day, 
        dateTimePickerFromTime.Value.Hour, dateTimePickerFromTime.Value.Minute, 
        dateTimePickerFromTime.Value.Second);

    DateTime toDateTime = new DateTime(dateTimePickerToDate.Value.Year, 
        dateTimePickerToDate.Value.Month, dateTimePickerToDate.Value.Day, 
        dateTimePickerToTime.Value.Hour, dateTimePickerToTime.Value.Minute, 
        dateTimePickerToTime.Value.Second);

    commanddb.CommandText =
    "SELECT * FROM testtab WHERE datatime >= @from and datatime < @to";
    commanddb.Parameters.AddWithValue("@from", fromDateTime);
    commanddb.Parameters.AddWithValue("@to", toDateTime);

Collectives™ on Stack Overflow

Adding time to SQL date query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related