Operand data type datetime is invalid for sum operator

Question

0.00/5 (No votes)

See more:

Hi I have this record in my table

CSS

DateId  Date                     Id
1       2013-06-01 00:00:00.000  1
2       2013-07-05 00:00:00.000  1
3       2013-08-08 00:00:00.000  1
4       2013-09-10 00:00:00.000  1

what I want is to get the total running sum of the Date field.

This is the query am using but am getting this error<<Operand data type datetime is invalid for sum operator.>>:

SQL

SELECT *, (SELECT DateDiff(DD,a.[Date],SUM(b.[Date]))
                       FROM [dbo].[Date] b
                       WHERE b.[DateId] <= a.[DateId]) as DateSum
   FROM   [dbo].[Date] a
   ORDER BY a.[DateId];

Can someone help

Posted 2-Oct-13 8:58am

El Dev

Add a Solution

Comments

Mehdi Gholam 2-Oct-13 15:10pm

What does a sum of date values mean?

El Dev 2-Oct-13 15:56pm

I want to get running sum of Date like suppose if I am writing this query:

SELECT *, (SELECT (sum(b.[Id])))
FROM [dbo].[Date] b
WHERE b.[DateId] <= a.[DateId]) as SumId
FROM [dbo].[Date] a
ORDER BY a.[DateId];
this will run and will give me the running total of the Id like this:

DateId Date Id SumId
1 2013-06-01 00:00:00.000 1 1
2 2013-07-05 00:00:00.000 1 2
3 2013-08-08 00:00:00.000 1 3
4 2013-09-10 00:00:00.000 1 4

this is what am looking for...

so for the date I want to get the difference btween the date then get the running sum by day and charge 10% where the difference between dates is greater than 30 days.

1 solution

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)

OriginalGriff · Answer 1 · 2013-10-02T09:14:00

Solution 1

It's making a very sensible complaint here: think about it.

What do you get is you add 3rd February 1914 to 8 June 2012?
Nothing that makes any sense.

So SQL is basically saying "I don't know how to do that: I have no idea what the result will be".
And I have to agree with it. There is nothing sensible you can get from adding dates together (unlike subtracting them, but even then, what the heck would you get if you try to subtract two dates from one date?)

Rethink what you are trying to do: it doesn't make any sense in the real world either!

Posted 2-Oct-13 9:14am

OriginalGriff

Comments

El Dev 2-Oct-13 16:02pm

ok suppose I want to get the difference between Dates if the difference is greater than 30 days I need to charge 10% as interest.can u help me resolve this?

OriginalGriff 3-Oct-13 4:17am

Between what dates?

El Dev 3-Oct-13 5:17am

Dates from above, look at my question again pliz!!I have shared the record that I have in my table.

I want to get running sum of Date like suppose if I am writing this query:

SELECT *, (SELECT (sum(b.[Id])))
FROM [dbo].[Date] b
WHERE b.[DateId] <= a.[DateId]) as SumId
FROM [dbo].[Date] a
ORDER BY a.[DateId];
this will run and will give me the running total of the Id like this:

DateId Date Id SumId
1 2013-06-01 00:00:00.000 1 1
2 2013-07-05 00:00:00.000 1 2
3 2013-08-08 00:00:00.000 1 3
4 2013-09-10 00:00:00.000 1 4

this is what am looking for Dates also.
so for the date I want to get the difference between the dates then get the running sum by day and charge 10% where the difference between dates is greater than 30 days.