locked
Query for difference between dates RRS feed

  • Question

  • I know I can use the DATEDIFF

    How can I write it if StartDate and EndDate are in these formats?

    StartDate: 2006-09-20 00:00:00.000

    EndDate:   2013-09-20 18:20:53.000

    I want to find the number of days between dates of dates in the two columns

    Monday, July 28, 2014 9:19 PM

Answers

  • Hi Bry,

    There are a variety of answers on the following link; -

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/6f487357-5b88-4c43-b29e-b288e3204f4f/difference-between-two-dates?forum=transactsql

    also check out further examples within; -

    DATEDIFF (Transact-SQL)

    DECLARE @dt1 DATETIME, @dt2 DATETIME
    SELECT
        @dt1 = '2006-09-20 00:00:00.000',
        @dt2 = '2013-09-20 18:20:53.000'
       
    SELECT DATEDIFF(day, @dt1, @dt2) AS DayDiff


    Kieran Patrick Wood http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood http://kieranwood.wordpress.com/


    Monday, July 28, 2014 9:46 PM
  • Hi, 

    select DATEDIFF(DD,'2006-09-20 00:00:00.000','2013-09-20 18:20:53.000'), you can find number of days.


    Nagendra

    Tuesday, July 29, 2014 9:26 AM
  • Those work great, What if I want to use values from a table?

    I have a column called StartDate and a column called EndDate with dates in it. I just what it to show the StartDate in a column, the Endate in column and the number of days in a column.

    Hi,

    Based on your description, do you want to display the following result?

    USE <DatabaseName> 
    GO
    
    --Create a table named DateTest
    CREATE TABLE DateTest
    (StartDate  datetime,
    EndDate   datetime
    )
    
     INSERT INTO DateTest
    VALUES  ('2006-09-20 00:00:00.000','2013-09-20 18:20:53.000'),
                     ('2007-05-06 12:10:09.000','2008-05-07 12:10:09.000'),
                     ('2007-05-07 09:53:01.000','2009-05-08 09:53:01.000'),
                       ('2008-08-07 05:53:01.000','2010-08-08 05:53:01.000')
    
    -- Create another table named DateTest1
    SELECT  DateTest.StartDate, DateTest.EndDate,
    DATEDIFF(DAY,DateTest.StartDate,DateTest.EndDate) AS days_number INTO DateTest1
    FROM DateTest
    
    SELECT *FROM DateTest1
    
    --Below is the result
    StartDate                              EndDate                              days_number
    2006-09-20 00:00:00.000                2013-09-20 18:20:53.000                2557
    2007-05-06 12:10:09.000                2008-05-07 12:10:09.000                367
    2007-05-07 09:53:01.000                2009-05-08 09:53:01.000                732
    2008-08-07 05:53:01.000                2010-08-08 05:53:01.000                731

    Thanks,
    Lydia Zhang


    • Marked as answer by Sofiya Li Tuesday, August 5, 2014 6:00 AM
    Wednesday, July 30, 2014 2:40 AM

All replies

  • Hi Bry,

    There are a variety of answers on the following link; -

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/6f487357-5b88-4c43-b29e-b288e3204f4f/difference-between-two-dates?forum=transactsql

    also check out further examples within; -

    DATEDIFF (Transact-SQL)

    DECLARE @dt1 DATETIME, @dt2 DATETIME
    SELECT
        @dt1 = '2006-09-20 00:00:00.000',
        @dt2 = '2013-09-20 18:20:53.000'
       
    SELECT DATEDIFF(day, @dt1, @dt2) AS DayDiff


    Kieran Patrick Wood http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood http://kieranwood.wordpress.com/


    Monday, July 28, 2014 9:46 PM
  • Hi, 

    select DATEDIFF(DD,'2006-09-20 00:00:00.000','2013-09-20 18:20:53.000'), you can find number of days.


    Nagendra

    Tuesday, July 29, 2014 9:26 AM
  • Those work great, What if I want to use values from a table?

    I have a column called StartDate and a column called EndDate with dates in it. I just what it to show the StartDate in a column, the Endate in column and the number of days in a column.

    Tuesday, July 29, 2014 12:28 PM
  • Those work great, What if I want to use values from a table?

    I have a column called StartDate and a column called EndDate with dates in it. I just what it to show the StartDate in a column, the Endate in column and the number of days in a column.

    Hi,

    Based on your description, do you want to display the following result?

    USE <DatabaseName> 
    GO
    
    --Create a table named DateTest
    CREATE TABLE DateTest
    (StartDate  datetime,
    EndDate   datetime
    )
    
     INSERT INTO DateTest
    VALUES  ('2006-09-20 00:00:00.000','2013-09-20 18:20:53.000'),
                     ('2007-05-06 12:10:09.000','2008-05-07 12:10:09.000'),
                     ('2007-05-07 09:53:01.000','2009-05-08 09:53:01.000'),
                       ('2008-08-07 05:53:01.000','2010-08-08 05:53:01.000')
    
    -- Create another table named DateTest1
    SELECT  DateTest.StartDate, DateTest.EndDate,
    DATEDIFF(DAY,DateTest.StartDate,DateTest.EndDate) AS days_number INTO DateTest1
    FROM DateTest
    
    SELECT *FROM DateTest1
    
    --Below is the result
    StartDate                              EndDate                              days_number
    2006-09-20 00:00:00.000                2013-09-20 18:20:53.000                2557
    2007-05-06 12:10:09.000                2008-05-07 12:10:09.000                367
    2007-05-07 09:53:01.000                2009-05-08 09:53:01.000                732
    2008-08-07 05:53:01.000                2010-08-08 05:53:01.000                731

    Thanks,
    Lydia Zhang


    • Marked as answer by Sofiya Li Tuesday, August 5, 2014 6:00 AM
    Wednesday, July 30, 2014 2:40 AM