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!

[sunny_talwar]

It seems to be working well for me:

LET vVAR = Num(Now());

Table:

LOAD LOCATION_END_DATE,

  Time(Floor(Frac(LOCATION_END_DATE), 1/(24*60)), 'h:mm TT') as LOCATION_END_TM;

LOAD TimeStamp($(vVAR) + MakeTime(0, 0, RecNo() - 1)) as LOCATION_END_DATE

AutoGenerate 60;

[mphekin12]

Sunny,

Thanks for the example.  I did find one case in your example where the end time doesn't match the end date/time:

Any thoughts on this?

[sunny_talwar]

Use the new approach that celambarasan‌ mentioned below. I think this is attributed to rounding errors:

Rounding Errors

[jonathandienst]

I think you need to use Round() rather than Floor() in your expression. Round will remove the seconds and go to the nearest minute, while Floor() simply removes the seconds, effectively always rounding down.

[mphekin12]

Thank you Celambarasan, this new code seems to be working correctly!  I appreciate your help.

[Anil_Babu_Samineni]

How about this

timestamp(floor(LOCATION_END_DATE*1440)/1440 as Location_END_TM

OR

timestamp#( timestamp( LOCATION_END_DATE, 'DD.MM.YYYY hh:mm'), 'DD.MM.YYYY hh:mm') as Location_END_TM

[mphekin12]

Jonathan,

Thanks for your response, but as stated above, I do not want to round.  I just need to truncate the seconds from the timestamp.

[mphekin12]

Thank you Sunny.  It does appear that Celambarasan's method works.