Data retrieving from SQL Server with C#

Question

I am trying to retrieve the data between two dates using C# with SQL Server database. I am passing dates entered in two text boxes to the sql statement as show below
"SELECT SUM(CONVERT(DECIMAL(6,2),howmuchspent)) AS totalexpenses FROM MONTHLYEXPENSES WHERE expensesdate BETWEEN ' " + fromdate.Text + " ' AND '" + todate.Text + "' ";
With the statement above I am getting data that from the required date range and also data that is out of date range too.
When I tested the statement in SQL Server query screen by giving dates as follows
SELECT SUM(CONVERT(DECIMAL(6,2),howmuchspent)) AS totalexpenses FROM MONTHLYEXPENSES WHERE expensesdate BETWEEN ' 03/01/2010 ' AND '03/13/2010'
I get data exactly between those dates.

What was the wrong I was doing in retrieving data programatically?

Any help is appreciated. Thank you

theLizard · Answer

i created a test table and inserted dates and amounts it did as expected 1 1/1/2010 12:00:00 AM 23.50 2 2/10/2010 12:00:00 AM 40.00 3 3/8/2010 12:00:00 AM 15.00 SELECT sum(spent)as total FROM tbl where dte >= '2010/01/01' AND dte SELECT sum(spent)as total FROM tbl where dte between '2010/01/01' AND '2010/03/01' it returned the values 63.50 which is correct. if you just want the values between but not including the from date and to date then do this SELECT sum(spent)as total FROM tbl where dte > '2010/01/01' AND dte this will give $40.00 as the result.

Prasad Vempati · Answer

Mr.Lizard I did entered 3 records with 3 dates 2010/12/03 2010/10/03 and 2010/25/02. When I tried to retrieve with fromdate=2010/01/02 and todate=2010/28/02 I still received all three dates. Thank you Prasad

theLizard · Answer

Convert your from date and to date to this format 2010/03/01 and 2010/03/13 it might not seem much but that is how SQL dates should be stored. Why? because storing dates in this fashion ensures that you will NOT have any problems with globalized date formats US dates are 03/01/2010 UK dates are 01/03/2010 stored as 2010/03/01 converts correctly for both when regional settings are applied. I don't think that using C# has anything to do with your problem.

Sam Hobbs · Answer

What is probably happening is that the data is not what you assume it is. The data you show here is probably not the data that actually exists. I agree that the data you posted is exactly the same for both except the data from the program might have a trailing space that the data you used interactively does not but that should make no difference. I see also that there is a leading and trailing space in the fromdate but that is the case in both so that should not be a problem.

Prasad Vempati · Answer

Mr Hobbs Thanx for your reply. I did look the data with Response.write statement. It is exactly giving the date as fromdate.text as 03/01/2010 and todate.text as 03/13/2010 Thanx again

Sam Hobbs · Answer

Look at the data in fromdate.Text and todate.Text ; it is probably not what you expect. When I say look I mean look at the data during execution; look to see for sure and without a doubt what is the data. Assuming the data is valid when the data is not valid is impossible to fix and no one here can tell you what the problem is without knowing for sure what the data is.