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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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