simple date subtraction - 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

SQL Server 2005 Forums

Transact-SQL (2005)

simple date subtraction

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-02-27 : 08:22:13

Hi,

I'm trying to subtract a date from another, to get the difference in minutes, except it's returning it in a date format such as

1900-01-01 00:03:42.127

getDate() - lastLoggedin as MinutesAgo

How can I get this returned as "3 minutes 42 seconds ago" or "222 seconds ago" or even "3 minutes ago" I am open to the best way to return this data that will be displayed on my web page.

Thanks very much!
mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-27 : 08:25:56

select cast(datediff(second, lastloggedin, getdate()) as varchar) + ' second(s) ago'
from yourtablenamehere

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-27 : 08:31:36

[code]
declare @t1 datetime,
@t2 datetime

select @t1 = '2007-02-27 21:25:31',
@t2 = '2007-02-27 23:57:27'

select a = convert(varchar(10), diff_sec) + ' seconds ago',
b = convert(varchar(10), diff_min) + ' minutes ago',
c = convert(varchar(10), diff_sec / 60) + ' minutes ' + convert(varchar(10), diff_sec % 60) + ' seconds ago'
from
(
select diff_sec = datediff(second, @t1, @t2),
diff_min = datediff(minute, @t1, @t2)
) d
/*
a b c
---------------------- ---------------------- -----------------------------------------
9116 seconds ago 152 minutes ago 151 minutes 56 seconds ago
*/
[/code]

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-02-27 : 08:57:59

perfect thanks again!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources