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
                    object objListaPlačil = cmd.ExecuteScalar();
                    if (objListaPlačil != DBNull.Value)
                        ListaPlačil = Convert.ToDecimal(objListaPlačil);
                return ListaPlačil;

Edited by Mitja Bonca: n/a

8 Years
Discussion Span
Last Post by Mitja Bonca

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.


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:

    // or

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';

Edited by privatevoid: n/a


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);
                    object objZnesek = cmd.ExecuteScalar();
                    if (objZnesek != DBNull.Value)
                        Znesek = Convert.ToDecimal(objZnesek);


Edited by Mitja Bonca: n/a

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.