Jump to content
  • 0

Criteria precision on time alone


Hencoco

Question

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?

Link to comment
Share on other sites

2 answers to this question

Recommended Posts

  • 0

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. 

image.thumb.png.0b9fcc1c8b43c4f90a6b7d95aaa09b47.png

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

image.png.deb0cb62a0af88a7531b9cfb886a5750.png

 

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

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...