How to do a select by date in C #?

Question

How to do a select by date in C #?

Navigation

#1 by (2 votes)
#2 by (1 votes)

2

I want to do a select by the current date, type: list all the data that was registered in my table today, I did it this way and it does not result in anything, it does not generate errors or list any of the information registered.

//Método Listar registos cadastrado Actualmente
public List<View_Caixa> Listar_Data()
{
    List<View_Caixa> lis = new List<View_Caixa>();
    SqlConnection conexao = new SqlConnection(caminho);
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = conexao;
    cmd.CommandType = CommandType.Text;

    string li = "select *from view_caixa where data_registo=@data_registo"; //É aqui onde eu faço o meu select
    cmd.CommandText = li;
    cmd.Parameters.AddWithValue("data_registo", DateTime.Now);
    conexao.Close();
    SqlDataReader dr;
    conexao.Open();
    dr = cmd.ExecuteReader();
    while (dr.Read())
    {
        View_Caixa obj = new View_Caixa();
        obj.id_caixa = Convert.ToInt32(dr["id_caixa"].ToString());
        obj.funcionario = dr["funcionario"].ToString();
        obj.caixa_inicial = Convert.ToDecimal(dr["caixa_inicial"].ToString());
        obj.total_veda_caixa = Convert.ToDecimal(dr["total_veda_caixa"].ToString());
        obj.total_venda_multicaixa = Convert.ToDecimal(dr["total_venda_multicaixa"].ToString());
        obj.total_multipagamento = Convert.ToDecimal(dr["total_multipagamento"].ToString());
        obj.caixa_final = Convert.ToDecimal(dr["caixa_final"].ToString());
        obj.conciliacao = Convert.ToDecimal(dr["conciliacao"].ToString());
        obj.data_registo = Convert.ToDateTime(dr["data_registo"].ToString());
        lis.Add(obj);
    }
    return lis;
}

sql-server c#

asked by anonymous 03.04.2017 / 13:29

2 answers

Pointer changes the value inside function in C ++ Are callback functions asynchronous, only with specific methods like setTimeout?

score 2 · Answer 1

The DateTime.Now returns the date and time and in your select you are asking to list only where the date is exactly the same as that returned in DateTime.Now .

Change your select to (Using SQL-Server ):

select * from view_caixa where data_registo between @data_registo and @data_registro2

On your DateTime.Now do this:

cmd.Parameters.AddWithValue("data_registo", DateTime.Now.ToString("dd/MM/yyyy"+" 00:00:00"));
cmd.Parameters.AddWithValue("data_registo", DateTime.Now.ToString("dd/MM/yyyy"+" 23:59:59"));

This way it will get all records from the bank within that specified period. Even if your bank is recording without the time, this will work, but note that, depending on the DB, you need to pass the parameter to the date. If it is oracle, for example, it would look like this:

select * from view_caixa where data_registo between 
   TO_DATE(@data_registo, 'DD/MM/YYYY HH24:mi:ss') and 
   TO_DATE(@data_registro2, 'DD/MM/YYYY HH24:mi:ss')

Check how your date format is in DB and how the query is being built.

score 1 · Answer 2

In the method call AddWithValue , the parameter name must contain the at sign.

Replace:

cmd.Parameters.AddWithValue("data_registo", DateTime.Now);

By:

cmd.Parameters.AddWithValue("@data_registo", DateTime.Now);

Edit and P.S .: George Wurthmann noticed the biggest problem in the code. His answer is correct. The date / time comparison with DateTime.Now does not work because it takes up to millisecond. You should compare only the date.