Remove Seconds from a Time Field

[mphekin12]

Hello,

I am having some issues with what seems like a very easy problem.  I want to remove the seconds (no rounding) from a time field.  I am currently using the following code but this doesn't seem to be working:

         time(frac(END_DATE), 'h:mm TT') as END_TM

The reason I know it's not working is that there are duplicate times when I set up a List Box:

If I override the document settings in the Number tab and select 'Time' as the data type, the list box displays the seconds:

Can someone tell me what I need to do to my code above to remove/truncate the seconds from the time?  Again, I do not want to round.

Thank you!

[CELAMBARASAN]

Try flooring the secs

Time(Floor(Frac(END_DATE), 1/(24*60)), 'h:mm TT') as END_TM

[mphekin12]

Celambarasan,

Thanks for the quick reply.  I have already tried that code and although the seconds were removed from the timestamp, the result didn't give the expected results in all situations.  Please see the highlighted records for examples.  In every highlighted case, I was expecting '12:02 AM' as the result.

Any other ideas?

[sunny_talwar]

I would assumed the above to have worked. Would you be able to share  a sample where this isn't working?

[mphekin12]

Sunny,

Please see the screenshot in my previous post.  There are several records where the time is 12:02 AM that are changed to 12:02 AM if I use the code provided by Celambarasan.  I have highlighted them in yellow so you can find them easily.

Thank you!

[sunny_talwar]

I see that, would you be able to share a screenshot of your script where you create End Time field?

[mphekin12]

Here you go:

[Chanty4u]

try this

,date(floor(timestamp#(Endtime,'MM/DD/YYYY hh:mm:ss'),'h:mm TT')) as record_date

,time(frac (timestamp#(Endtime,'MM/DD/YYYY hh:mm:ss'),'h:mm TT')) as record_time

[CELAMBARASAN · Accepted solution]

Yes, there is a problem in floor function, in most of the cases this seems not working.

As a work around you can try this

Time#(Text(Time(Frac(END_DATE), 'h:mm TT')), 'h:mm TT') as END_TM

[mphekin12]

Chanty 4u,

I had to change your code around a little because the syntax was wrong.  I changed it to:

Unfortunately no data was returned with this code.

Thanks for your help though!