CODEWITHSUNDEEP
sql-servert-sqldatetime
Tim
Tim

Reputation: 7431

Custom SQL datediff function

has anyone seen any custom code examples for determining a sensible difference between two dates. i.e similar to that on facebook.

Upvotes: 0

Views: 1124

Answers (2)

Tim
Tim

Reputation: 7431

Thanks for the help, in this scenario i think it would be too complex and to achieve nicely in SQL, added to the fact that clientside logic would be more efficient as i intended to use jQuery to update these values ala facecrack.

Found this library which works perfectly for my purposes.

Upvotes: 0

podiluska
podiluska

Reputation: 51504

While agreeing that this may be better done at the presentation layer, this could form the basis of a solution in SQL - if you need it on your SQL Server, you could of course also write a CLR function in the .Net language of your choice.

declare @d datetime = '2012-10-11 00:52'
select 
    case 
        when diff < 60 then convert(varchar(5), DATEDIFF(s, @d, getdate())) + ' seconds'
        when diff < 3600 then convert(varchar(5), DATEDIFF(MI, @d, getdate())) + ' minutes'
        when diff < 86400 then convert(varchar(5), DATEDIFF(hh, @d, getdate())) + ' hours'
        when diff < 604800 then convert(varchar(5), DATEDIFF(D, @d, getdate())) + ' days'
        when diff < 2419200 then convert(varchar(5), DATEDIFF(WEEK, @d, getdate())) + ' weeks'
        else convert(varchar(5), DATEDIFF(MONTH, @d, getdate())) + ' months'
    end
from
    (select DATEDIFF(s, @d, getdate()) as diff) v

Upvotes: 2

Related Questions