current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free.
up vote 0 down vote favorite

In facebook, on each status, i can see the time between the creation date and now date. Ex. 1 hour ago, six months ago, 1 year ago,...

So, in my database SQL, i want to create a View that include a column that describe the time between creationDate and NowDate - NVARCHAR(30) may be include the string: 1 day ago, 2 months ago, etc.

I have one solution for compare 2 DateTime variable but it is not clear:

  1. I will caculate the time between DateCreation and Now as TimeInterval
  2. If TimeInterval < 1 minute -> "Just Now", If TimeInterval >= 1min and <60min -> caculate the minute between 2 TimePoints. Assume that 6 min -> "6min(s) ago"
  3. Similarly, for a period of months or years.

Is there any other solution for this.

share|improve this question
    
What is the exact format you want to display it in? – Szymon Nov 3 '13 at 2:49
    
Exactly i want, the column (nvarchar): 1 month ago, 2 days ago, etc. – Vũ Tuấn Anh Nov 3 '13 at 2:50
    
I have edited my question – Vũ Tuấn Anh Nov 3 '13 at 3:03

2 Answers 2

up vote 2 down vote accepted

You could use the code similar to below to generate your data. It's not very precise for months as I used 30 days for a month. For years, I used 365 days per year. But I guess that should be precise enough.

declare @date1 as datetime = '20131103 13:00:40'
declare @date2 as datetime = '20160208 16:40:45'

select case 
    when datediff(ss, @date1, @date2) < 60 THEN cast(datediff(ss, @date1, @date2) as varchar(max)) + ' seconds ago'
    when datediff(ss, @date1, @date2) < 3600 THEN cast((datediff(ss, @date1, @date2) / 60) as varchar(max)) + ' minutes ago'
    when datediff(ss, @date1, @date2) < 86400 THEN cast((datediff(ss, @date1, @date2) / 3600) as varchar(max)) + ' hours ago'
    when datediff(ss, @date1, @date2) < 2592000 THEN cast((datediff(ss, @date1, @date2) / 86400) as varchar(max)) + ' days ago'
    when datediff(ss, @date1, @date2) < 31536000 THEN cast((datediff(ss, @date1, @date2) / 2592000) as varchar(max)) + ' months ago'
    else cast((datediff(ss, @date1, @date2) / 31536000) as varchar(max)) + ' years ago'
end

The numbers in the query come from the number of seconds in:

  • minute = 60
  • hour = 3600
  • day = 86400
  • month = 2592000
  • year = 31536000

An easier version that does not use just seconds is:

declare @date1 as datetime = '20131003 13:00:40'
declare @date2 as datetime = '20151101 16:40:45'

select case 
    when datediff(ss, @date1, @date2) < 60 THEN cast(datediff(ss, @date1, @date2) as varchar(max)) + ' seconds ago'
    when datediff(mi, @date1, @date2) < 60 THEN cast((datediff(mi, @date1, @date2)) as varchar(max)) + ' minutes ago'
    when datediff(hh, @date1, @date2) < 24 THEN cast((datediff(hh, @date1, @date2)) as varchar(max)) + ' hours ago'
    when datediff(dd, @date1, @date2) < 30 THEN cast((datediff(dd, @date1, @date2)) as varchar(max)) + ' days ago'
    when datediff(mm, @date1, @date2) < 13 THEN cast((datediff(mm, @date1, @date2)) as varchar(max)) + ' months ago'
    else cast((datediff(yy, @date1, @date2)) as varchar(max)) + ' years ago'
end
share|improve this answer
    
I consider that the month 30 days or 31 days, you have any suggestion? – Vũ Tuấn Anh Nov 3 '13 at 3:07
    
I don't think it really matters as no one is going to expect precision when it comes to months. A month has 30.4 days on average so it's closer to 30. – Szymon Nov 3 '13 at 3:09
    
My system is design to decision maker and he/she care about the exactly month or year for balance sheet. Anyway, tks for your help. – Vũ Tuấn Anh Nov 3 '13 at 3:15
up vote 1 down vote

I would put this in a function like:

 create function dbo.TimeDiffToWords(@eventDateTime as DateTime)
    returns varchar(100)
    as 
    begin
    declare @ret varchar(100)
    declare @value int
    declare @done bit

set @done = 0

set @value = datediff(year, @eventDateTime, getdate())

if @value > 0
begin
    set @ret = 'Over '+cast(@value as varchar(6))+' year(s) ago.'
    set @done = 1
end

if @done = 0
begin
  set @value = datediff(month, @eventDateTime, getdate())
    if @value > 0
    begin
        set @ret = 'Over '+cast(@value as varchar(6))+' months(s) ago.'
        set @done = 1
    end
end

if @done = 0
begin
  set @value = datediff(day, @eventDateTime, getdate())
    if @value > 0
    begin
        set @ret = 'Over '+cast(@value as varchar(6))+' days(s) ago.'
        set @done = 1
    end
end

-- continue till down to seconds ago.
if @done = 0
begin
    set @ret = 'Not coded yet.'
end

return @ret

end

then you can just call it like this test code does:

select dbo.TimeDiffToWords(EventDateTime)
from (select dateadd(month,-40, getdate()) as EventDateTime
    union all select dateadd(day,-40, getdate())
    union all select dateadd(day,-6, getdate())
    union all select dateadd(day,-2, getdate())
    union all select dateadd(hour,-4, getdate())
    union all select dateadd(minute,-2, getdate())
) as testTimes
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.