Wednesday, September 17, 2008

Get the date diference in the form of x Years y months z days

Somethimes we need to get the difference of two dates in SQL server to send the result to front end. The result of DateDiff SQL function will give the difference of the date for a particular part like year or month or day... If you think of just concatinating the results got from three datediff function with parameters for year,month and day, you will get the wrong result. WHY???
DateDiff(Year,'2007/31/12','2008/1/1') is 1 though the difference only 1 day.
Similarly month differnce and day difference will be shown as 1.
So the result age is "1 year 1 month 1 day". But actyually only "1 day".
Here is a function i wrote to get the exact value. This is very useful when you need to get the age on today when you have DOB stored in your database. Here you have to send the DOB as @startdate and GetDate() as @endDate

Create FUNCTION [dbo].[fn_GetDateDifference] (@startDate as DateTime,@endDate DateTime)
RETURNS varchar(50) AS
DECLARE @res varchar(50)
DECLARE @mon int,@year int,@day int
SELECT @year = DateDiff(year,@startDate,@endDate)
--SET @year = @day/365
SET @startDate = DateAdd(Year,@Year,@startDate)
if(@startDate > @endDate) --If the month value of Start Date is more than that of End Date
--@startDate can become more than date 2 if the values are like
--@startDate = '2007/12/15' and @endDate = '2008/02/01',
--The datediff year will be 1 though only 2 month+ difference
SET @year = @year - 1
SET @startDate = DateAdd(Year,-1,@startDate)
SELECT @mon = DateDiff(Month,@startDate,@endDate)
--SET @mon = @day/28
--Select @mon = DateDiff(Month,@startDate,@endDate)
SET @startDate = DateAdd(Month,@mon,@startDate)
if(@startDate > @endDate)
--Same as year,month can show maximum of 1 unit more if the start date "Day" val in more than end date "Day value"
set @startDate = DateAdd(Month,-1,@startDate)
set @mon = @mon - 1
SELECT @day = DateDiff(Day,@startDate,@endDate)
set @res = ''
If(@year > 0)
SELECT @res = Cast(@year as Varchar(10)) + ' Year(s) '
if( @year>0 or @mon>0)
Set @res = @res + Cast(@mon as Varchar(10)) + ' Month(s) '

set @res = @res + Cast(@day as Varchar(10)) + ' Day(s)'
--set @res = @year & ' '
--select @res = @year & ' ' & @mon & ' ' &@day

Note: It is not a good practice to use backend sqlserver for this kind of calculations. It is preferable to return two dates to front end and then do the desired calculations.

No comments: