DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to calculate the difference between dates?

The following Microsoft SQL Server T-SQL database scripts in SSMS Query Editor demonstrate the calculation of difference between dates using the DATEDIFF and user-defined functions.

DATEDIFF returns the (signed integer) count of the datepart (year, month, day, etc.) boundaries crossed between the start date and end date.

 

-- T-SQL environment control statement

USE [tempdb]

GO

 

-- T-SQL date difference queries

 

SELECT Years=DATEDIFF(year, getdate(), '2011-01-01 00:00:00.0000000');

 

SELECT Quarters=DATEDIFF(quarter, getdate(), '2011-01-01 00:00:00.0000000');

 

SELECT Months=DATEDIFF(month, getdate(), '2011-01-01 00:00:00.0000000');

 

SELECT Days=DATEDIFF(dayofyear, getdate(), '2011-01-01 00:00:00.0000000');

 

SELECT Days=DATEDIFF(day, getdate(), '2011-01-01 00:00:00.0000000');

 

SELECT Weeks=DATEDIFF(week, getdate(), '2011-01-01 00:00:00.0000000');

 

SELECT Hours=DATEDIFF(hour, getdate(), '2011-01-01 00:00:00.0000000');

 

SELECT Minutes=DATEDIFF(minute, getdate(), '2011-01-01 00:00:00.0000000');

 

SELECT Seconds=DATEDIFF(second, getdate(), '2011-01-01 00:00:00.0000000');

 

SELECT Milliseconds=DATEDIFF(millisecond, getdate(), dateadd(hh, 2,getdate()));

 

SELECT Nanoseconds=convert(int, DATEDIFF(nanosecond, getdate(), dateadd(second, 2,getdate())));

 

-- Date difference in HH:MM:SS format

select convert(varchar,dateadd(minute, datediff(minute, getdate(), dateadd(second, 10000, getdate())),'1900-01-01'),24)

 
-- DATETIME subtraction maps difference to base date 1900-01-01
SELECT CONVERT(datetime,'2012-02-01 11:11:11') - '2010-12-25 01:01:02'
-- 1901-02-08 10:10:09.000

-- Delta: 1 year 1 month 8 days 10 hours 10 minutes 9 seconds

 

-- SQL calculate date difference in years, months, days

-- SQL table-valued function - SQL user-defined function - UDF

-- SQL datetime difference

USE AdventureWorks2008;

GO

CREATE FUNCTION fnDateDifference  (@StartDate DATETIME,@EndDate DATETIME)

RETURNS @Delta TABLE(Years  INT,

                   Months INT,

                   Days   INT)

AS

  BEGIN

    DECLARE  @Anniversary DATETIME

    SET @Anniversary = Dateadd(yy,Datediff(yy,@StartDate,@EndDate),@StartDate)

    

    INSERT @Delta

    SELECT Datediff(yy,@StartDate,@EndDate) - (CASE

                                                 WHEN @Anniversary > @EndDate THEN 1

                                                 ELSE 0

                                               END),

           0,

           0

    UPDATE @Delta     SET    Months = Month(@EndDate - @Anniversary) - 1

    UPDATE @Delta     SET    Days = Day(@EndDate - @Anniversary) - 1

    RETURN

  END

GO

 

-- Test table-valued UDF

SELECT *

FROM   fnDateDifference ('1956-10-23', GETDATE())

SELECT *

FROM   dbo.fnDateDifference ('1956-10-23', GETDATE())

       /* Results

Years       Months      Days

52          4           1

*/

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE