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 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
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