Vipul Posted June 18, 2018 Report Share Posted June 18, 2018 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.. Quote Link to comment Share on other sites More sharing options...
0 MayMusic Posted June 19, 2018 Report Share Posted June 19, 2018 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 Quote Link to comment Share on other sites More sharing options...
0 Vipul Posted June 20, 2018 Author Report Share Posted June 20, 2018 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... Quote Link to comment Share on other sites More sharing options...
0 Vipul Posted June 20, 2018 Author Report Share Posted June 20, 2018 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 Quote Link to comment Share on other sites More sharing options...
0 Vipul Posted November 25, 2019 Author Report Share Posted November 25, 2019 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 Quote Link to comment Share on other sites More sharing options...
Question
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..
Link to comment
Share on other sites
4 answers to this question
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.