Click here to Skip to main content
14,971,661 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hi,

I want to sum of a column in sql database its working fine when today date selected if I am selecting previous date it add previous to current date data(like today is 6/10/2013 and data value is 2,3,4 and previous date is 4/10/2013 and data value is 5,6,7 when I am selecting previous date it show the sum of total value 1+2+3+4+5+6+7.
It would be 5+6+7.

My query is

query = "select sum(txtPurity) from EntryTable where txtCustomerName=@Name AND txtDate >=@Date AND cmbItemType='Gl'";
Posted
Comments
Mike Meinz 6-Oct-13 9:20am
   
Almost always problems related to dates are because the date column in the database table is not declared as DATE or DATETIME data type and/or the parameter passed to the SQL Select is not declared as DATE or DATETIME data type. I suspect that either the database column containing the date and/or the @date parameter are not of DATE data type. That is the cause of your problem.

If you always follow the best practice of using DATE or DATETIME data type to store dates and always using DATE or DATETIME data type parameters to pass dates, you will greatly reduce problems using dates.
indiancodinglove 6-Oct-13 21:30pm
   
I am using sql2005 there is no option for date data type only datetime datatype.
Mike Meinz 6-Oct-13 21:39pm
   
Then use DateTime data type!

If you only use date values, the time part of the DateTime data type will be zero.
Kornfeld Eliyahu Peter 6-Oct-13 9:20am
   
txtDate >= @Date

for 4/10/2013 it will include 4th, 5th and 6th also!!!
OriginalGriff 6-Oct-13 10:02am
   
I suggest that you post this as the solution: it's very likely that this is exactly what he needs!

Hey there, If you want data for a specific date, then why are you using >= operator?
It will definitely include data for @Date as well as for dates after this date.

just use txtDate = @Date .

Let me know if solves the problem.

Azee...
   
SQL
txtDate <= @Date


for 4/10/2013 it will include 4th, 5th and 6th also!!!
   
Almost always problems related to dates are because the date column in the database table is not declared as DATE or DATETIME data type and/or the parameter passed to the SQL Select is not declared as DATE or DATETIME data type. I suspect that either the database column containing the date and/or the @date parameter are not of DATE data type. That is the cause of your problem. If a DATETIME data type is used and you only use dates and not times, then time part of the DATETIME value will be zero.

If you always follow the best practice of using DATE or DATETIME data type to store dates and always using DATE or DATETIME data type parameters to pass dates, you will greatly reduce problems using dates.

See DateTime data type[^]

See Configuring Parameters and Parameter Data Types[^] DateTime type
   
now I using sql2008 and which gives a option for date data type.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900