Getting a sum of numbers with DateTime parameter

[Mitja Bonca]

I have dates and times stored in the database - the data type of DateTime (for example: 17.1.2010 16.23.34 - dd.MM.yyyy hh.mm.ss)
The parameter which I define it has too look ONLY of date ("dd.MM.yyyy"), because I want to get all the values on a specific date and the sum them up.

This is the reason why DateTime.Today doesn`t work, and either it doesn`t DateTime.Today.Date.

So how to declate that it will be correct, that the parameter will look only for date (without time)?
ReplyQuoteMark As AnswerEditDelete

decimal ListaPlačil = 0;
                string danesDobiček = @"SELECT SUM(Znesek) FROM Plačilo WHERE DatumPlačila = @danes";
                using (SqlCommand cmd = new SqlCommand(danesDobiček, povezava))
                {
                    cmd.Parameters.Add("@danes", SqlDbType.DateTime);
                    cmd.Parameters["@danes"].Value = DateTime.Today.Date; //This is not the correct parameter, thats why it does not read the database
                    povezava.Open();
                    object objListaPlačil = cmd.ExecuteScalar();
                    if (objListaPlačil != DBNull.Value)
                        ListaPlačil = Convert.ToDecimal(objListaPlačil);
                    povezava.Close();
                }
                return ListaPlačil;

[apegram]

There's probably something you can do with SQL Server date formatting functions. An alternative would be to reference your date like this

string sql = "Select Count(*) From MyTable Where MyDate >= @datemin and MyDate < @datemax";

In this case, @datemin would be the date you want, @datemax would be @datemin + 1.

[privatevoid]

I am not 100% sure what you actually want to know.

If the time in the database is 'DateTime' or 'SmallDateTime' then no matter how you pass a date it will store it with hours mins and seconds. If you just pass a date then the database will contain a date with a time of 00:00:00.

The best way to pass dates as strings is in universal time format e.g.
'2010-02-18T00:00:00' = 'yyyy-MM-ddTHH:mm:ss'.

In C# you can format date strings like this:

myDate.ToString("yyyy-MM-ddTHH:mm:ss");
    // or
    DateTime.Today.Date.ToString("yyyy-MM-ddTHH:mm:ss");

If you want to select any items were the date is '2010-01-18'
then we can use the following SQL:

SELECT SUM(Znesek) FROM Plačilo 
WHERE DatumPlačila BETWEEN '2010-01-18T00:00:00' AND '2010-01-18T23:59:59';

[Mitja Bonca]

I got it. This is what I wanted:

decimal Znesek = 0;
                string danesDobiček = @"SELECT SUM(Znesek) FROM Plačilo WHERE (DatumPlačila >= @začetniČas) and (DatumPlačila < @končniČas)";
                using (SqlCommand cmd = new SqlCommand(danesDobiček, povezava))
                {
                    int ŠteviloDniRazmika = 1; 
                    DateTime ZačetniDatum = DateTime.Today; 
                    DateTime KončniDatum = ZačetniDatum.AddDays(ŠteviloDniRazmika);

                    cmd.Parameters.AddWithValue("@začetniČas", ZačetniDatum);
                    cmd.Parameters.AddWithValue("@končniČas", KončniDatum);
                    povezava.Open();
                    object objZnesek = cmd.ExecuteScalar();
                    if (objZnesek != DBNull.Value)
                        Znesek = Convert.ToDecimal(objZnesek);
                    povezava.Close();

Salved!