Age

July 14, 2004



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








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers