# Calculate the difference between two dates

Discussion in 'SQL Server 2005 General Developer Questions' started by Bruno, Oct 2, 2008.

1. ### BrunoNew Member

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
2. ### ghemantModerator

Hi,
Welcome to the forums!!
Refer datediff() in Books Online, this will calculate the difference between two dates.
3. ### FrankKalisModerator

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
4. ### AdriaanNew Member

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.[]
5. ### BrunoNew Member

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.
6. ### AdriaanNew Member

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!
7. ### AdriaanNew Member

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()
8. ### destroyerNew Member

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.....[:'(]
9. ### FrankKalisModerator

This should work:
select * from tbl_Invoice where DATEDIFF(GETDATE(),[Date])<30
select * from tbl_Invoice where DATEDIFF(DAY, GETDATE(),[Date])<30
10. ### MadhivananModerator

To make use of index, useselect
* from tbl_Invoice where [date]
>=DATEDIFF(DAY, DATEDIFF(DAY,0,GETDATE()),0)[date]
<DATEDIFF(DAY, DATEDIFF(DAY,0,GETDATE()),31)
11. ### AdriaanNew Member

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)
12. ### destroyerNew Member

Thanks.....its working.....
[] .................................Thanks...Once again........
13. ### MadhivananModerator

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