Problem in sum of sql data

Question

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 6-Oct-13 3:13am

indiancodinglove

Add a Solution

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!

4 solutions

Solution 2

SQL

txtDate <= @Date

for 4/10/2013 it will include 4th, 5th and 6th also!!!

Posted 6-Oct-13 6:46am

Kornfeld Eliyahu Peter

Solution 3

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

Posted 6-Oct-13 15:53pm

Mike Meinz

Solution 4

now I using sql2008 and which gives a option for date data type.

Posted 7-Oct-13 18:18pm

indiancodinglove

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

**Azee** · Accepted Answer · 2013-10-06T06:13:00

Solution 1

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...

Posted 6-Oct-13 6:13am

Azee