Text and numeric values in calculation

[Sam23]

I have a calculated field on a Datapage calculating age using DateDiff. I would then like to add text, such as 'y/o', but get a validation error,  I am guessing because the DateDiff is a number and then I am continuing on with text. What is the syntax to handle this? Thanks so much!!

[Kurumi]

Hi @Sam23 - you can convert your whole formula to text and then add the 'y/o'. You can do this as reference:
 

CONVERT(varchar(10),Datediff(year, [@field:DOB], SysUTCDateTime())) + ' years old'

Result:

Hope this helps!

[ParkLoey]

Hi there! Just wanted to drop Caspio's function reference. It has a sample of the solution @Meekeee provided above.

https://howto.caspio.com/function-reference/

- LOEY

[Sam23]

Thanks so much Park and Meekee! Fixed my issue.

[Sam23]

MeeKee or other:

Now I want to put the DOB in the same expression, following the age. I'd like the format mm-dd-yyyy. Here is what I have that doesn't work:

CONVERT(varchar(20),FORMAT([@field:K2_Contact_Birthday],"mm-dd-yyyy"))

this gives me an invalid formula. Help! Thanks again.

[PotatoMato]

Hello, @Sam23! If you wish to change the date format, you may use the formula below.

CONVERT(DATETIME,[@field:K2_Contact_Birthday],110)

For list of different format, you may check this link: https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/

-Potato

[RuisiHansamu]

Hi, you may also want to check out some of the datediff function that you can refer here as well: https://howto.caspio.com/faq/reports-datapages/calculated-fields-and-datediff-function/