CODEWITHSUNDEEP
c#sqltimedatetimepicker
Alex Alex
Alex Alex

Reputation: 15

Adding time to SQL date query

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

Upvotes: 0

Views: 431

Answers (2)

Mohsen Ebnetorab
Mohsen Ebnetorab

Reputation: 1

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

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

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.

Upvotes: 2

Related Questions