Time format

[Barbie]

Hi, I have this calculation formula: 

CASE WHEN CONVERT(VARCHAR, [@field:Date], 108) = '00:00:00' THEN ' ' ELSE CONVERT(VARCHAR, [@field:Date], 108) END

I need to do is get the format to be AM/PM. Right now the result is 19:00:00 it should be 7:00 AM. How to achieve that?

[Kuroshi]

Hi @Barbie

If you want to have that format, please use the formula below:

CASE WHEN CONVERT(VARCHAR, [@field:Date], 108) = '00:00:00' THEN ' ' ELSE CONVERT(VARCHAR,CAST('[@field:Date]' AS TIME),100) END

I hope this works now.

- Batchini

[Barbie]

Thank you @Batchinithat worked. But how can we add the space between minute and AM/PM?

[Kuroshi]

That's great, @Barbie. You can use this formula instead: 

REPLACE(REPLACE(CONVERT(varchar(15), CAST([@field:Date] AS TIME), 100), 'P', ' P'), 'A', ' A')

I hope this satisfies your wrkflow now. 

[NailDyanC]

Just to add in the workaround above, you may consider reviewing these links: 

https://howto.caspio.com/datapages/reports/advanced-reporting/calculations-in-forms-and-reports/

 

[cheonsa]

On 3/30/2021 at 10:42 AM, Barbie said:

Hi, I have this calculation formula: 

CASE WHEN CONVERT(VARCHAR, [@field:Date], 108) = '00:00:00' THEN ' ' ELSE CONVERT(VARCHAR, [@field:Date], 108) END

I need to do is get the format to be AM/PM. Right now the result is 19:00:00 it should be 7:00 AM. How to achieve that?

Hi @Barbie,

You can also change the formatting of the result of this Formula. It is only applicable if you are using  a DataPage. However, if it is in the Table level, you may use the other solution provided in this thread.

[ParkLoey]

In addition to @LittleMsGinger's suggestion, here is an additional resource for you. It is about field formatting.

-  LOEY

 

[Kurumi]

Hi - For Time only fields, you can check this new forum post: 

 

[Kurumi]

Hi! Caspio now offers integration with OpenAI, so you can use extensions to leverage AI to update your data based on a prompt. Here's a sample use case to get the time only based on the provided Date/Time field.

Request:
Get the time in "[@field:Date_Submitted]" and change it to 12-hr time.
Determine if it is AM or PM.
Return only the final result in the format of:
HH:MM AM or PM

Result:

Change the 'Date_Submitted' field to your right field name.

To learn more about Extensions, you can check it here: https://howto.caspio.com/integration/extensions/