critera Criteria precision on time alone

[Hencoco]

I want to be able to search a table according to the criteria of a “From Time” (greater than equal to) and a “To Time” (less than equal to), the problem is that there is no option for Time, only Date AND Time. The result should be ALL records from all dates between the specified time. Eg. Created on (Timestamp) From 06h00 To 08h00 à display records created between 06h00 and 08hoo across all of table history.

Currently I am using a submission form with a virtual field to pass the parameter to the search form. I have one Timestamp field and three Date/Time fields I wish to search.

Is there any way to setup a criteria with only a time precision?

[cheonsa]

Hi @Hencoco,

 

 

In order to extract the time from a Timestamp, you need to use a formula field and use functions like SELECT convert(varchar(8), getdate (), 108) . However, this will be converted as a String and you cannot filter the time with a String DataType. 

 

What you can do is to get only the hour using this function: Datepart(hh,[@field:time]) It will return as an integer. 

On your Reports DataPage, you can use that formula field as search criteria. You can set it to Dropdown then use custom Value. 

 

For more information, you can check these links: https://howto.caspio.com/function-reference/

https://stackoverflow.com/questions/12354699/time-part-of-a-datetime-field-in-sql

 

Hope this helps.

 

-kristina

[Hencoco]

Thanks Kristina! It works great! I was able to add additional search and criteria for minutes...