Age

>>Script Language and Platform: SQL
This script finds the years, months, days, hours minutes and seconds between two dates.


— Usage
select dbo.age(”11/01/1974”, getdate()) — returns 28 year(s) 7 month(s) 10 day(s) 14:40:16
— This is more accurate than using datediff

Author: DCR



CREATE FUNCTION dbo.age(@start datetime, @end datetime)
RETURNS varchar(42) — select len(‘999 year(s) 99 month(s) 99 day(s) 99:99:99′) — 42 characters
AS
BEGIN

— declare @start datetime, @end datetime select @start = ’01/01/1996 07:30:00’, @end = getdate()

declare @year int
declare @month int
declare @counter_date datetime

select @year = 0
select @month = 0
select @counter_date = @start

while @counter_date < @end begin select @year = @year + 1 select @counter_date = dateadd(year, 1, @counter_date) end if @counter_date > @end
begin
select @year = @year – 1
select @counter_date = dateadd(year, -1, @counter_date)
end

while @counter_date < @end begin select @month = @month + 1 select @counter_date = dateadd(month, 1, @counter_date) end if @counter_date > @end
begin
select @month = @month – 1
select @counter_date = dateadd(month, -1, @counter_date)
end

— select @counter_date
— select ‘Age’ = convert(varchar, @year) + ‘ year(s) ‘ + convert(varchar, @month) + ‘ month(s) ‘ + dbo.ddhhmmss(@counter_date, @end)

RETURN convert(varchar, @year) + ‘ year(s) ‘ +
convert(varchar, @month) + ‘ month(s) ‘ +
dbo.ddhhmmss(@counter_date, @end)

END




Disclaimer:
We hope that the information on these script pages is
valuable to you. Your use of the information contained in these pages,
however, is at your sole risk. All information on these pages is provided
“as -is”, without any warranty, whether express or implied, of its accuracy,
completeness, or fitness for a particular purpose…

Disclaimer Continued

Back to Database Journal Home

Latest Articles