Need to display Age fo the Record in hours, min and day format

[Vipul]

I have Record created Date Field in my table now in my Tabular report result page i need to display Age of the record in this format

If Record is created today then display in Hrs & Min ( 1hrs 15min ago)

If Record is created 1 day ago then need to display (1 day ago)

So can someone help to create custom Calculation Field scrip for this requirement ?

Thanx in Advance..

 

[MayMusic]

You can try calculated field and CASE statement for instance 

CASE 

WHEN Datediff(hour, [@field:Date], GetUTCDate()) <24 THEN CAST(Datediff(hh, [@field:Date], GetDate()) % 24 AS varchar) + 'hrs' +   CAST(Datediff(mi, [@field:Date], GetDate()) % 60 AS varchar) + 'mins'

ELSE CAST(Datediff(dd, [@field:Date], GetDate()) AS varchar) + 'days' + CAST(Datediff(hh, [@field:Date], GetDate()) % 24 AS varchar) + 'hrs' +   CAST(Datediff(mi, [@field:Date], GetDate()) % 60 AS varchar) + 'mins'

END

 

[Vipul]

HI MayMusic,
Thanx for your answer... its working but there is some issue...  FYI..my current date time of the pc is 06-20-2018 11:22 AM (I have also attached screenshot)

- Record created on 06/20/2018 00:42:12   is displayiing (-2hrs-3mins)
- Record created on 06/19/2018 21:52:49 is displaying (1hrs47mins )
- Record created on 06/19/2018 05:18:47 is displaying (0days17hrs21mins)

so my query is why it is displaying result in - value for record created today and can we discard 0 days if record is for today and if its older than today then we need to display value in days only not require hrs and min...
 

[Vipul]

Hi MayMusic,

We got this issue fixed.. I m just updating here so it can be helpful for someone else too...

To implement this solution you need a Timestamp Data Type field and Calculated fields.

Let us say that the name of the Timestamp Data Type field is "Timestamp".

*****
Than Calculated field 1 will be looked like the following:

CASE
WHEN GetDate()>[@field:Timestamp]

THEN 
GetDate()-[@field:Timestamp]

ELSE [@field:Timestamp]-GetDate()
END

*****

And Calculated field 2 will be:

CASE
WHEN DatePart(day,[@calcfield:1]) = 1

THEN 
Convert(nvarchar, DatePart(hour,[@calcfield:1]))+':'
+Convert(nvarchar, DatePart(minute,[@calcfield:1]))+' hours ago'

ELSE Convert(nvarchar, DatePart(day,[@calcfield:1]))+' days ago'
END

*****

Also, if you would like, you can have all of this code in one Calculated field:

CASE
WHEN DatePart(day,
CASE
WHEN GetDate()>[@field:Timestamp]

THEN 
GetDate()-[@field:Timestamp]

ELSE [@field:Timestamp]-GetDate()
END) = 1

THEN 
Convert(nvarchar, DatePart(hour,
CASE
WHEN GetDate()>[@field:Timestamp]

THEN 
GetDate()-[@field:Timestamp]

ELSE [@field:Timestamp]-GetDate()
END))+':'
+Convert(nvarchar, DatePart(minute,
CASE
WHEN GetDate()>[@field:Timestamp]

THEN 
GetDate()-[@field:Timestamp]

ELSE [@field:Timestamp]-GetDate()
END))+' ago' 

ELSE Convert(nvarchar, DatePart(day,
CASE
WHEN GetDate()>[@field:Timestamp]

THEN 
GetDate()-[@field:Timestamp]

ELSE [@field:Timestamp]-GetDate()
END))+' days ago'
END
 

 

[Vipul]

Hi I am using same function

but its not working what is the mistake i am making here ?

 

CASE
WHEN DatePart(day,
CASE
WHEN GetDate()>[@field:createdate]

THEN 
GetDate()-[@field:createdate]

ELSE [@field:createdate]-GetDate()
END) = 1

THEN 
Convert(nvarchar, DatePart(hour,
CASE
WHEN GetDate()>[@field:createdate]

THEN 
GetDate()-[@field:createdate]

ELSE [@field:createdate]-GetDate()
END))+':'
+Convert(nvarchar, DatePart(minute,
CASE
WHEN GetDate()>[@field:createdate]

THEN 
GetDate()-[@field:createdate]

ELSE [@field:createdate]-GetDate()
END))+' ago' 

ELSE Convert(nvarchar, DatePart(day,
CASE
WHEN GetDate()>[@field:createdate]

THEN 
GetDate()-[@field:createdate]

ELSE [@field:createdate]-GetDate()
END))+' days ago'
END