Converting a date time stamp into hourly intervals non military with an 'AM/PM' designation

[danaleota1]

Hello I have the following datetimestamp field : 'YYYY-MM-DD HH:MM:SS.000000' that I want to convert into an hourly interval non- military.  I first tried hour(SEGSTART_EST)&':00' as Hour_Created, but this won't work.  This is an example of the original datetimestamp field and what I would like the output to be.  

Thank you in advance. 

SEGSTART_EST	Hour_Created
2019-12-31 01:12:29.000000	1:00 AM
2019-12-31 07:50:47.000000	7:00 AM
2019-12-31 10:04:09.000000	10:00 AM
2020-03-13 15:16:52.000000	3:00 PM

[Ksrinivasan]

hi,

try this

=Timestamp(SEGSTART_EST,'hh:mm TT')

ksrinivasan

[Ksrinivasan]

hi,

i have checked this

Time(SEGSTART_EST)

result ok

ksrinivasan

[danaleota1]

Hi Ksrinivasan,

=Timestamp(SEGSTART_EST,'hh:mm TT') 

 does return the time back in non military however I need it to return at the bottom of the hour like a floor() would.  So it the time returned 2:31 PM I would want it to return 2:00 PM.   

[Ksrinivasan]

hi Danaleotal,

=Timestamp(round('2019-12-31 01:12:29.000000', (1/24)), 'hh:mm TT')

=Timestamp(round(SEGSTART_EST, (1/24)), 'hh:mm TT') as TIME

ksrinivasan

[danaleota1 · Accepted solution]

I used:  Time(Class(Frac(SEGSTART_EST), 1/24), 'hh:mm TT') as Hour_Created3,

and it gave me the desired output.  

[danaleota1]

Thanks again Ksrinivasan,

I also tried yours but if it's more than half past the hour it rounds up which i don't want. this one did the trick however, 

Time(Class(Frac(SEGSTART_EST), 1/24), 'hh:mm TT') 

I found this solution on another thread btw. 

[Ksrinivasan]

hi,

many ways are there to reach destination,

did you tried my recommendation? and what result,

ksrinivasan