Home > SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2 > How to get difference between two dates – TSQL

How to get difference between two dates – TSQL


To calculate the difference between two dates, you can use DATEDIFF() function. The DATEDIFF() function returns the number of days/month/years and time between two dates.

Syntax:

DATEDIFF (date part, start date, end date)

 

For example, to calculate time left till Independence Day, you can use:

— © 2011 – Vishal (http://SqlAndMe.com)

 

DECLARE     @Today      DATETIME = GETDATE()

DECLARE     @IDay       DATETIME = '2011-08-15 08:30:00'

 

SELECT DATEDIFF(DAY, @Today, @IDay),      'Days Left'

UNION ALL

SELECT DATEDIFF(MONTH, @Today, @IDay),    'Months Left'

UNION ALL

SELECT DATEDIFF(YEAR, @Today, @IDay),     'Years Left'

UNION ALL

SELECT DATEDIFF(QUARTER, @Today, @IDay),  'Quarters Left'

UNION ALL

SELECT DATEDIFF(HOUR, @Today, @IDay),     'Hours Left'

UNION ALL

SELECT DATEDIFF(MINUTE, @Today, @IDay),   'Minutes Left'

UNION ALL

SELECT DATEDIFF(SECOND, @Today, @IDay),   'Seconds Left'

Result Set:

———– ————-

86          Days Left

3           Months Left

0           Years Left

1           Quarters Left

2052        Hours Left

123122      Minutes Left

7387278     Seconds Left

 

(7 row(s) affected)

To see all possible values for datepart check BOL.

 

Hope This Helps!

Vishal

  1. senthil
    03.01.2012 02:29 | #1

    SELECT DATEDIFF(MM,@StartDate,@EndDate) +
    CASE WHEN DATEDIFF(MM,@EndDate,@StartDate) > 0 THEN
    CASE WHEN DATEPART(DD,@StartDate)=1 THEN 0 ELSE 1 END
    + CASE WHEN DATEPART(DD,DATEADD(DD,1,@endDate))=1 THEN 0 ELSE 1 END
    ELSE
    1
    END

  1. No trackbacks yet.