How to account for hours - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to account for hours

Author

Topic

craigwg
Posting Yak Master

154 Posts

Posted - 2012-07-17 : 16:37:26

I have a query that is intended to count the elapsed time from a date in the past to today. Easy enough if I wanted a day count ("It's been 45 days since event A"), but the problem is that after 370 days I would want it to say "It's been 1 year and 5 days since event A".

I think I've accounted for years and months, but I can't quite figure out the math for days. Here is my current code:


/*
--total time
select cast(datediff(year, '2004-08-29 07:00:00.000',getutcdate()) /10 as char) + ' decades'
union all
select cast(datediff(year, '2004-08-29 07:00:00.000',getutcdate())as char) + ' years'
union all
select cast(datediff(month, '2004-08-29 07:00:00.000',getutcdate())as char) + ' months'
union all 
select cast(datediff(day, '2004-08-29 07:00:00.000',getutcdate())as char) + ' days'
union all
select cast(datediff(minute, '2004-08-29 07:00:00.000',getutcdate())as char) + ' minutes'
union all
select cast(datediff(second, '2004-08-29 07:00:00.000',getutcdate())as char) + ' seconds'
*/

declare @weddate datetime
declare @years int
declare @months int
declare @days int
declare @hours int
declare @minutes int
declare @seconds int
declare @sql nvarchar(2000)

set @weddate='2004-08-29 19:30:00.00'

--Calculate values
set @years = datediff(year, @weddate,getutcdate())
set @months = datediff(month, @weddate,getutcdate())
set @days = datediff(day, @weddate,getutcdate())
set @hours = datediff(hour, @weddate,getutcdate())
set @minutes = datediff(minute, @weddate,getutcdate())
set @seconds = datediff(second, @weddate,getutcdate())

select @sql= CASE 
	WHEN @years = 0 
	THEN '' 
	ELSE cast(@years as varchar)+ ' years, ' 
END

select @sql=@sql + CASE 
	WHEN @months = 0 
	THEN '' 
	ELSE cast(@months-1-((@years-1)*12) as varchar)+ ' months, ' 
END

select @sql=@sql + CASE 
	WHEN @days = 0 
	THEN '' 
	ELSE cast(@days-((@years-1)*365) as varchar)+ ' days, ' 
END

select @sql=@sql + CASE 
	WHEN @hours = 0 
	THEN '' 
	ELSE cast(@hours-((@days-1)*24) as varchar)+ ' hours, ' 
END

select @sql=@sql + CASE 
	WHEN @minutes = 0 
	THEN '' 
	ELSE cast(@minutes-((@hours-1)*60)-60 as varchar)+ ' minutes, ' 
END

select @sql=@sql + CASE 
	WHEN @seconds = 0 
	THEN '' 
	ELSE cast(@seconds-((@minutes-1)*60)-60 as varchar)+ ' seconds.' 
END

select @sql=' Since event A ' + @sql
	select @sql = @sql 
print @sql

If there is a more efficient way to get my desired output I'm open to ideas.

Thanks!

Craig Greenwood

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-17 : 18:00:24

Take a look at this thread to see if that helps at all. I don't recall the details and/or limitations of the query that I posted, and it is somewhat complicated. Visakh's query was simpler, but it made some approximations: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=170186

craigwg
Posting Yak Master

154 Posts

Posted - 2012-07-19 : 11:46:22

Handy. I'll have to work that logic in. It's sexier than mine! Thanks.

Craig Greenwood

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources