SQL Server Performance

Calculate the difference between two dates

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

  1. Bruno New 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. ghemant Moderator

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

    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. Adriaan New 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.[:p]
  5. Bruno New 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. Adriaan New 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. Adriaan New 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. destroyer New 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. FrankKalis Moderator

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

    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. Adriaan New 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. destroyer New Member

    Thanks.....its working.....
    [:)] .................................Thanks...Once again........
  13. Madhivanan Moderator

    Yes. The question is ambigious. Post sample data with expected result
  14. Kobojunkie New Member

Share This Page