Jump to content
  • 0

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


Vipul

Question

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

  • 0

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

 

Link to comment
Share on other sites

  • 0

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...
 

caspdate.png

Link to comment
Share on other sites

  • 0

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
 

 

Link to comment
Share on other sites

  • 0

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

 

Link to comment
Share on other sites

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.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...