CODEWITHSUNDEEP
c#sql-serverdatetimecomparison
Rodrigo Mota
Rodrigo Mota

Reputation: 3

Get todays records from SQL using C#

Within a DB I have a table that has afield named Data Recolha, I checked its data type and it's datetime. The valued stored is as follows: 26/08/2019 5:00:00 

string hoje = DateTime.Today.AddDays(-1).ToString("dd/MM/yyyy H:mm:ss.fff");

string query = "SELECT * FROM  database WHERE  CodCliente=1 AND 'Data Recolha' >= '" + hoje + "'" ;

I've also tried converting to date, used cast on both values, I either get an error or when I run the code it displays all values stored in the table. Also used different used hoje as DateTime as string with and without format. The values are stored in a C# DataTable and I checked the type of the value gathered from field Data Recolha and it's stored as System.DateTime.

Upvotes: 0

Views: 104

Answers (2)

Wei Lin
Wei Lin

Reputation: 3821

  1. please using sqlparameter not string concat to avoid sqlinjection
  2. if you're using DataReader or DataTable to read data,i'll recommend you to use Dapper to simplify code. 
using(var connection = yourconnection)
{
    datetime hoje = DateTime.Today.AddDays(-1);
    string sql = "SELECT * FROM  database WHERE  CodCliente=1 AND 'Data Recolha' >= @hoje" ;
    var result = connection.Query(sql,new{hoje});
}

Upvotes: 1

forpas
forpas

Reputation: 164204

What your code does is string comparison because:
'Data Recolha' is not the name of the column Data Recolha since you enclosed it in single quotes, and
hoje is a string literal.
So enclose Data Recolha in square brackets and convert hoje to datetime with CONVERT() like this:

[Data Recolha] >=  convert(datetime, hoje, 103)

So use this:

string query = "SELECT * FROM  database WHERE  CodCliente=1 AND [Data Recolha] >= convert(datetime, '" + hoje +"', 103)";

Upvotes: 0

Related Questions