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 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to calculate employment service time and age?

Execute the following Microsoft SQL Server T-SQL example script in Management Studio Query Editor to calculate the difference between two dates in years, months and days.

-- SQL calculate age - Calculating age in years from DOB - Age calculation

-- SQL calculate length of service / employment in years, months, days

-- SQL calculating duration / delta / difference between two dates

-- T-SQL datediff datetime function - TSQL dateadd datetime function

USE AdventureWorks;

DECLARE  @EndDate             DATETIME,

         @Anniversary         DATETIME,

         @StartDate           DATETIME,

         @YYDelta             INT,

         @MMDelta             INT,

         @DDDelta             INT

 

SET @StartDate = '2000-01-01'

 

SET @EndDate = Getdate()

 

SET @Anniversary =

      Dateadd(yy,Datediff(yy,@StartDate,@EndDate),@StartDate)

 

SET @YYDelta = Datediff(yy,@StartDate,@EndDate) -

(CASE

   WHEN @Anniversary > @EndDate THENELSE 0   END)

 

SET @MMDelta = Month(@EndDate - @Anniversary) - 1

 

SET @DDDelta = Day(@EndDate - @Anniversary) - 1

 

SELECT Years  = @YYDelta,

       Months = @MMDelta,

       [Days] = @DDDelta

GO

 

/* Results

 

Years       Months      Days

9           1           19

*/

------------

Related link:

http://sqlusa.com/bestpractices/datetimeconversion/

 

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