CODEWITHSUNDEEP
c#sql-server
Lreyes527
Lreyes527

Reputation: 15

Trying to pull records in between two time ranges from SQL Server

I want to display a count on a label for all the rows that fall between 6AM and 8AM. I have a datetimepicker that I want to also use to select the day for all the rows that should be counted. I have tried several different ways to format my SELECT statement but nothing has worked. I either get datetime conversion error or wrong syntax.

var sql = "SELECT RTFPressTableID" +
      "FROM RTFPressTables" +
      "WHERE PressCloseTime BETWEEN CONVERT(DATE, 'Date 06:00:00:000 AM',109) >= @Date AND CONVERT(DATE, 'Date 08:00:00:000 AM',109) <= @Date";
using (SqlConnection conn = new SqlConnection("Data Source = ; Initial Catalog = ; Integrated Security = True"))
{
    conn.Open();
    using (SqlCommand command = new SqlCommand(sql, conn))
    {
        command.Parameters.Add("@Date", SqlDbType.DateTime);
        command.Parameters["@Date"].Value = dateTimePicker1.Value.Date;
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
        }
    }
}

Upvotes: 0

Views: 99

Answers (3)

Nilson Martins
Nilson Martins

Reputation: 49

You need to give spaces in your quotation marks after plus to avoid string concatenation. Something like this:

"SELECT RTFPressTableID" +
" FROM RTFPressTables" +
" WHERE PressCloseTime

Upvotes: 0

Dale K
Dale K

Reputation: 27225

I think you want the following:

var sql = @"SELECT RTFPressTableID
    FROM RTFPressTables
    WHERE PressCloseTime BETWEEN DATEADD(day, DATEDIFF(day, 0, @Date), '06:00:00') AND DATEADD(day, DATEDIFF(day, 0, @Date), '08:00:00')";
using (SqlConnection conn = new SqlConnection("Data Source = ; Initial Catalog = ; Integrated Security = True"))
{
    conn.Open();
    using (SqlCommand command = new SqlCommand(sql, conn))
    {
        command.Parameters.Add("@Date", SqlDbType.Date);
        command.Parameters["@Date"].Value = dateTimePicker1.Value.Date;
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
        }
    }
}

Which has 3 changes from your code:

  1. Spaces are added to the SQL string as mentioned in the comments. In fact using a multi-line string (verbatim string literal) which is much easier to use for SQL strings.
  2. The datatype of the @Date parameter is now of type date instead of datetime.
  3. Most importantly the between statement is corrected to add the times to the date provided and use them as the limits.

Ref: Add Time to DateTime

Upvotes: 1

sticky bit
sticky bit

Reputation: 37472

What date time should "'Date 06:00:00:000 AM'" represent?

And why do you use "BETWEEN" and then ">=" resp. "<=" in it's arguments? That's not the right syntax. Either use BETWEEN or >= and <=. Though I guess < is actually what you want for the second operator.

You probably want:

var sql = @"
SELECT RTFPressTableID
       FROM RTFPressTables
       WHERE PressCloseTime >= dateadd(hour, 6, @Date)
             AND PressCloseTime < dateadd(hour, 8, @Date);";
...

Upvotes: 3

Related Questions