Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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 try below. Assuming that your Time filed is in proper timestamp format then may be like below
round(frac(TimeField)*24) as Hour
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
Hi Vegar,
Thanks, your solution worked!
BR
Shelly