SQL statement with datetimepicker

Question

This should hopefully be a simple one. When using a date time picker in a windows form, I want an SQL statement to be carried out, like so:

string sql = "SELECT * FROM Jobs WHERE JobDate = '" + dtpJobDate.Text + "'";

Unfortunately, this doesn't actually provide any results because the JobDate field is stored as a DateTime value. I'd like to be able to search for all records that are on this date, no matter what the time stored may be, any help?

New query:

        SqlDataAdapter da2 = new SqlDataAdapter();
        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "SELECT * FROM Jobs WHERE JobDate >= @p_StartDate AND JobDate < @p_EndDate";
        cmd.Parameters.Add ("@p_StartDate", SqlDbType.DateTime).Value = dtpJobDate.Value.Date;
        cmd.Parameters.Add ("@p_EndDate", SqlDbType.DateTime).Value = dtpJobDate.Value.Date.AddDays(1);
        cmd.Connection = conn;
        da2.SelectCommand = cmd;
        da2.Fill(dt);
        dgvJobDiary.DataSource = dt;

Huge thanks for all the help!

Answer 1

Just one answer: use parametrized queries.

This is for different reasons:

• security (no risk of SQL Injection
• no longer those problems for which you're opening a topic
• performance.

Thus, write your statement like this:

SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT * FROM Jobs WHERE JobDate = @p_Date"
cmd.Parameters.Add ("@p_Date", SqlDbType.DateTime).Value = dtpJobDate.Value;

If you want to ignore the time, then I think the best bet is to do a range search, if the time is stored in the DB, that is. Something like this (just the SQL query):

SELECT * FROM Jobs WHERE JobDate >= @p_StartDate AND JobDate < @p_EndDate

StartDate would then be dtpJobDate.Value.Date, and EndDate would be dtpJobDate.Value.Date.AddDays(1)

If the Time is not stored in the DB, then you can do this:

SELECT * FROM Jobs WHERE JobDate = @p_Date

where the search argument should be dtpJobDate.Value.Date

Answer 2

First of all - you have left a door open for SQL injection in your example.

Other than that - to answer your question, you'll have to drop the times off of the JobDate column to get the match done. Try something like this (SQL Injection code left in example for comparison)...

string sql = "SELECT * FROM Jobs WHERE CAST(CONVERT(CHAR(8), JobDate, 112) AS DATETIME) = '" + dtpJobDate.Text + "'";

If you were to parameterize your query - you could do it something like this...

using (var conn = new SqlConnection(myConnectionString))
using (var cmd = new SqlCommand("SELECT * FROM Jobs WHERE JobDate = @JobDate", conn))
{
    cmd.Parameters.Add(new SqlParameter("@JobDate", dtpJobDate.Value));

    conn.Open();
    using (var reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            // your code here to deal with the records...
        }
    }
}

Answer 3

Try dtpJobDate.Value.

Answer 4

Other than the SQL injection stuff in other answers, you can use something like this:

dtpJobDate.Value.ToString("yyyyMMdd HH:mm:ss");

But probably you won't find anything with exact time match, so you can change your query for something like

string sql = "SELECT * FROM Jobs WHERE JobDate BETWEEN '" + dtpJobDateStart.Value.ToString("yyyyMMdd HH:mm:ss") + "' AND '" + + dtpJobDateEnd.Value.ToString("yyyyMMdd HH:mm:ss") + " + "'";