Hello,

 I'm trying to write a query which will calculate the total between a start date in a table and today's date.

 Could someone advise?

Thanks,Bruno

Hi,

Welcome to the forums!!

Refer datediff() in Books Online, this will calculate the difference between two dates.

What "Total" do you want to calculate?

Can you give some more information? I guess you're after a something like

SELECT SUM()
FROM table
WHERE date BETWEEN start AND end
GROUP BY something

And perhaps he wants to know the difference between the totals for two different dates, with the sum calculated for one day, or cumulatively.

It takes some work to get a straight question here.

Yes. The question is ambigious. Post sample data with expected result

Thank you for the feedback.

I select the last row of the START_DT colomn and when I have that, I need to know the difference in days between that date and today's date in days?

let's say that the latest row is '12/05/08' from START_DT, I need to know how many days there are between then and today.

 I hope its a bit more clear.

Okay - that's much simpler. You confused us by using "total" in your original question.

The "last row" is a classic problem here. In a table, any row can be the last row - you have to decide which way to order the data, in order to know what is the "last row".

You seem to be looking for the most recent START_DT, rather than "the START_DT on the last row". This is the basic query:

SELECT MAX(START_DT)
FROM MyTable

Now you elaborate on MAX(START_DT), putting it inside DATEDIFF() with the proper parameter for days. You can use the GETDATE() function to get the current date.

That's it!

By the way, can there be rows in the table where START_DT is in the future?

It doesn't make a difference for the calculation itself, but if you want to use the most recent (but not future) START_DT, please add a WHERE clause

SELECT ............
FROM MyTable
WHERE START_DT <= GETDATE()

I would suggest you use the DATEADD function  If you are trying to calculate the total in days, months, years etc

http://publib.boulder.ibm.com/infocenter/rbhelp/v6r3/index.jsp?topic=/com.ibm.redbrick.doc6.3/ssg/ssg51.htm 

 hi...

am trying to calculate the date difference between the System date and the table date.....

the query which i wrote....

select * from tbl_Invoice where DATEDIFF("GETDATE()","Date")<30

[** where Date is the tbl_Invoice field name]

But the problem is it not working ....it is showing error.....

Plz.....help me.....

This should work:

select * from tbl_Invoice where DATEDIFF(GETDATE(),[Date])<30

select * from tbl_Invoice where DATEDIFF(DAY, GETDATE(),[Date])<30

To make use of index, use

select * from tbl_Invoice

[date]>=DATEDIFF(DAY, DATEDIFF(DAY,0,GETDATE()),0)

[date]<DATEDIFF(DAY, DATEDIFF(DAY,0,GETDATE()),31)

Good point about the index, but those DATEDIFF expressions will only return the number of days since day 0 and the number of days since day 31 ...

WHERE dbo.tblInvoice.[date]
BETWEEN DATEADD(DAY, DATEDIFF(DAY,0,GETDATE()), 0)
AND DATEADD(DAY, DATEDIFF(DAY,0,GETDATE()),31)

 Thanks.....its working.....

.................................Thanks...Once again........