datediff variance

[bookish]

Hi! I would like my report to show the difference between the start date of an activity and the birthday of a participant. It seems that datediff does not work as per my studies. Say the activity starts on Jan 1 and the birth day is Jan 3, I would like the column to show, 0 (for the month), 2 (for the days). Is this possible in Caspio?

[Bangalore]

I am sure this article will help you

http://howto.caspio.com/faq/time-and-da ... erson.html

[bookish]

Thank you, Bangalore for the suggestion and sorry for the late reply. I had tried it why you posted your reply. But it does show only the age by month and day granularity. Perhaps I just don't know how to tweak it to suit my purpose.

[Bangalore]

Did you look for more variations here:

http://msdn.microsoft.com/en-us/library/ms189794.aspx

http://howto.caspio.com/datapages/creat ... ed-fields/

[ManokNaPula]

Hey there,

I believe you have a similar function like this inquiry.

On 1/2/2019 at 10:44 AM, Scarlet said:

Hi @roattw

I have the same workflow but I somehow found a workaround for this.

 

I created 3 Virtual fields, one for Year calculation, one for month, and another one for year. 

Inside the virtual fields, here are my formula.

For Year:

Datediff(mm, [@field:Birthdate], GetUTCDate())/12

For Month:

Datediff(day, [@field:Birthdate], GetUTCDate())/30 - ([@cbParamVirtual1]*12)

For Day:

Datediff(day, [@field:Birthdate], GetUTCDate()) - ([@cbParamVirtual1]*365) - ([@cbParamVirtual2]*30)

 

And then I concatenated them all to the field that is on my table:

CONVERT(VARCHAR, [@cbParamVirtual1]) + "YEARS " + 
CONVERT(VARCHAR, [@cbParamVirtual2]) + "MONTHS " + 
CONVERT(VARCHAR,[@cbParamVirtual3]) + "DAYS"

 

This was my output.

 

I also hid the Virtual fields using HTML Block so it won't show on the DataPage.

 

Hope this helps!

 

Click this LINK to see the forum conversation.

 

Hope this helps!