CODEWITHSUNDEEP
c#sqlsql-server-2008-r2
Marco Dinatsoli
Marco Dinatsoli

Reputation: 10570

compare current date in database

I have a field in database with a DateTime format.

I want to do a query to extract all the rows that have this day, but that field in database has this format: 2014-11-07 21:21:22.733, I don't want to check the hours, seconds and minutes, I need to check just the day.

I did this:

string query = @"SELECT *
                 FROM Orders
                 WHERE PrintTime is NULL
                   AND CONVERT(DATE, Datelogged) = @Datelogged
                 ";
command.Parameters.AddWithValue("@Datelogged", DateTime.Today);

but I kept having empty results.

Upvotes: 0

Views: 595

Answers (2)

Nicholas Carey
Nicholas Carey

Reputation: 74177

You don't say what data type your date/time column is ("2014-11-07 21:21:22.733" is just a textual representation of the data in the column).

Assuming that your DateLogged column is defined as datetime or datetime2 rather than, say, varchar(32), you can do something like this:

public DataTable Fetchorders( DateTime dt )
{
  DataTable dt = new DataTable() ;

  using ( SqlConnection conn = new SqlConnection( "..." ) )
  using ( SqlCommand    cmd  = conn.CreateCommand()       )
  using ( SqlDataAdapter sda = new SqlDataAdapter(cmd)    ) 
  {
    string query = @"
      SELECT o.*
      FROM Orders o
      WHERE o.DateLogged >= @date
        and o.DateLogged <  dateadd(day,1,@date)
      " ;

    cmd.CommandText = query ;
    cmd.CommandType = CommandType.Text ;
    cmd.Parameters.Add(
       new SqlParameter( "@date" , dt.Date , SqlDbType.DateTime )
       ) ;

    conn.Open() ;
    sda.Fill(dt) ;
    conn.Close() ;

  }

  return dt ;
}

You want to avoid things like convert(date,t.DateLogged) because that converts the column into an expression and makes it impossible for the SQL Server optimizer to make use of any covering indices. So rather than seeking to rows in question, you might get a table scan instead.

Upvotes: 1

Ricardo Vera
Ricardo Vera

Reputation: 1

Try this:

Use GETDATE() to get the current date and time from the SQL Server.

string query = "SELECT * FROM Orders WHERE PrintTime is NULL AND CONVERT(DATE, Datelogged) = CONVERT(DATE,GETDATE())";

Upvotes: 0

Related Questions