Friendly date like Twitter in T-SQL

Question

0.00/5 (No votes)

See more:

Good Day

i have a SQL function which does not seem to do what it needs to do , like you have in twitter where they compare the current datetime the user is vieweing the post and the time it was added to give you something like 3 seconds ago, now , 3 min ago , 1d ago

declare @RECEIVER_USER_ID int = 5304
declare @CompareDate DATETIME  ='2021/07/25 7:08:35 PM'
declare @USER_CURRENT_DATE DATETIME  = '2021-07-24 20:58:10.000'
declare @s nvarchar(48)
set @s='Now'
select top 1 @s=convert(nvarchar,abs(n))+' '+s+case when abs(n)>1 then 's' else '' end+case when n>0 then ' ago' else ' ago ' end from (
 select convert(int,(convert(float,(@comparedate-@USER_CURRENT_DATE))*n)) as n, s from (
  select 1/365 as n, 'Year' as s union all
  select 1/30, 'Month' union all
  select 1, 'Day' union all
  select 7, 'Week' union all
  select 24, 'Hour' union all
  select 24*60, 'Minute' union all
  select 24*60*60, 'Second'
 ) k
) j where abs(n)>0 order by abs(n)
select   convert(varchar,convert(date, @CompareDate)) + '   ' + @s

This gives me

2021-07-25   6 Weeks ago

Which is not correct

What I have tried:

i have a UDF which i tried to change around several times

Posted 25-Jul-21 7:29am

Vimalsoft(Pty) Ltd

Updated 25-Jul-21 23:52pm

Add a Solution

Comments

Richard Deeming 29-Jul-21 6:01am

NB: This code really doesn't belong in the database. It's a presentation issue - the formatting should be done in the UI layer.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2021-07-25T23:52:00

I really couldn't follow what your code was attempting to do so I did this from scratch. I'm not claiming it is the most elegant way of doing this but it works.

SQL

declare @USER_CURRENT_DATE DATETIME = getdate();
declare @CompareDate DATETIME ='2021/07/25 7:08:35 PM';   -- Example test date

declare @res table ([id] int, [period] varchar(8), [amount] int);
INSERT INTO @res
SELECT 1, 'Year', datediff(YEAR, @compareDate, @USER_CURRENT_DATE) 
UNION
SELECT 2, 'Month', datediff(MONTH, @compareDate, @USER_CURRENT_DATE) 
UNION
SELECT 3, 'Week', cast(datediff(DAY, @compareDate, @USER_CURRENT_DATE) / 7 AS INT)
UNION
SELECT 4, 'Day', datediff(DAY, @compareDate, @USER_CURRENT_DATE) 
UNION
SELECT 5, 'Hour', datediff(HOUR, @compareDate, @USER_CURRENT_DATE) 
UNION
SELECT 6, 'Minute', datediff(MINUTE, @compareDate, @USER_CURRENT_DATE) 
UNION
SELECT 7, 'Second', datediff(SECOND, @compareDate, @USER_CURRENT_DATE);

select top 1 cast([amount] as varchar(3)) + ' ' + case when [amount] > 1 then [period] + 's ago' else [period] + ' ago' end as ago
from @res
where [amount] > 0
order by id;

I brute-force calculated all the possible differences between the dates i.e. years, months, weeks, days, hours, minutes, seconds. Add to that as you wish.

NOTE: The calculation for weeks does not use datepart WEEK! This is because that date falls into "last week" rather my interpretation of a week ago being 7 to 13 days ago. If you want something different then change the calculation for [period] = 'Week'

Note 2: Do not use an identity column for [id] because you cannot control the order in which these items are inserted into the table variable.

The solution just finds the "first" non-zero entry in the order that I have explicitly set.

For testing I used a variety of @CompareDate values built using variations of this statement:

SQL

declare @CompareDate DATETIME  =DATEADD(YEAR, -3, @USER_CURRENT_DATE)