Add days to a date and format it without a timestamp

[JKSGT]

Hi,

I could do with some wisdom on how to calculate a date. 

I'm trying to set a date in a field using a CASE statement. I have Virtual Field 1 which is capturing today's date without timestamp (pls see image). I also have a number (in this case 30) stored in Virtual Field 4.

I am trying to add the two together and have the result formatted without time stamp (e.g. 25 May 2021) but when I do this I get 31 Jan 1900. See my CASE statement below, the red part is the part that's not working.

CASE

WHEN DateDiff(day, SysUTCDateTime(), [@field:Date_1]) > [@cbParamVirtual4]

THEN DateAdd(day, [@cbParamVirtual4],CONVERT(DATE, '[@cbParamVirtual1]', 103))

ELSE [@field:Date_1]

END

Many thanks

Jay

[CoopperBackpack]

Hello @JKSGT,

It looks like the issue is related to the data type you use in the CONVERT() function.

Please try to convert to the datetime, so the formula have the following syntax:

 

CASE

WHEN DateDiff(day, SysUTCDateTime(), [@field:Date_1]) > [@cbParamVirtual4]

THEN DateAdd(day, [@cbParamVirtual4],  CONVERT(datetime, '[@cbParamVirtual1]', 103))

ELSE [@field:Date_1]

END

 

[CoopperBackpack]

Also, I wanted to add, that if you set the field that has Date/Time data type to the Calculated Value, you may use the Formatting options. 

You may select the format from Localization or set the Custom format:

 

These articles can be helpful: 

https://howto.caspio.com/localizations/formatting-options/

https://howto.caspio.com/datapages/datapage-components/field-formatting-options/

 

[JKSGT]

Many thanks, I'll give it a try.