How to convert a date & time field to hours

[ShellyG]

Hi there,

I have a field which contains data in the following format:

12/31/2017 12:07:09 PM

12/31/2017 12:07:14 PM

12/31/2017 10:04:20 PM

etc...

I want to convert this field on a script level to hours. For instance:

12/31/2017 12:07:09 PM should be - 12

12/31/2017 10:04:20 PM should be - 22

and so on...

Can someone help? 

[Kushal_Chawda]

@ShellyG try below. Assuming that your Time filed is in proper timestamp format then may be like below

round(frac(TimeField)*24) as Hour

[Vegar · Accepted solution]

Try this

LOAD
DateTime,
HOUR(Timestamp#(DateTime, 'MM/DD/YYYY hh:mm:ss TT')) as Hour
inline[
DateTime
12/31/2017 12:07:09 PM
12/31/2017 12:07:14 PM
12/31/2017 10:04:20 PM
];

If your DateTime field already is recognized as a timestamp then you could just use

HOUR(DateTime) as Hour

[ShellyG]

Hi Vegar,

Thanks, your solution worked!

BR

Shelly