Get todays records from SQL using C#

Question

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.

Answer 1

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