Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All ,
Below is my data
SYSTEM - 01/04/2015 01:28:51 PM# |
SYSTEM - 01/04/2015 01:49:07 PM# |
SYSTEM - 01/04/2015 02:05:21 PM# |
SYSTEM - 01/04/2015 02:09:13 PM# |
SYSTEM - 01/04/2015 02:09:38 PM# |
SYSTEM - 01/04/2015 02:13:22 PM# |
In the above data , I have to take only the hour and min .
The Time has to converted to 24 Hours Format.
Need input on this
Try this:
Time(TimeStamp#(Mid('SYSTEM - 01/04/2015 01:28:51 PM#', 10, 22), 'MM/DD/YYYY hh:mm:ss TT'), 'hh:mm') as Time
Tried it in a text box and got the following output
This should also work
=Time(Time#(Mid('SYSTEM - 01/04/2015 01:28:51 PM#', 22, 10), 'hh:mm:ss TT'), 'hh:mm')
LOAD
Time_Stamp,
Timestamp(Timestamp#(Trim(Mid(Time_Stamp,20,12)),'hh:mm:ss TT'),'hh:mm ') as H24_MM;
Temp:
LOAD * INLINE [
Time_Stamp
SYSTEM - 01/04/2015 01:28:51 PM#
SYSTEM - 01/04/2015 01:49:07 PM#
SYSTEM - 01/04/2015 02:05:21 PM#
SYSTEM - 01/04/2015 02:09:13 PM#
SYSTEM - 01/04/2015 02:09:38 PM#
SYSTEM - 01/04/2015 02:13:22 PM#
];
One more..
Load Field,
Time(Trim(SubField(SubField(Field,'-',2),'#',1)),'HH.MM') as Hours;
LOAD * INLINE [
Field
SYSTEM - 01/04/2015 01:28:51 PM#
SYSTEM - 01/04/2015 01:49:07 PM#
SYSTEM - 01/04/2015 02:05:21 PM#
SYSTEM - 01/04/2015 02:09:13 PM#
SYSTEM - 01/04/2015 02:09:38 PM#
SYSTEM - 01/04/2015 02:13:22 PM#
];
sunindia , You are always faster than me . However, in the above mid function, start parameter 22 may not work if the hour number is 10, 11 or 12.
Regards,
KKR
Time(TimeStamp#(Mid('SYSTEM - 01/04/2015 01:28:51 PM#', 10, 22), 'MM/DD/YYYY hh:mm:ss TT'), 'hh:mm') as HrMin
Hope this will help u...
Regards,
Mohammad
Hi,
Try this sample script
Data:
LOAD
DatTime,
Timestamp(Timestamp#(Replace(DatTime,'SYSTEM - ', ''),'MM/DD/YYYY hh:mm:ss TT#'),'hh:mm') as Time;
Temp:
LOAD * INLINE [
DatTime
SYSTEM - 01/04/2015 01:28:51 PM#
SYSTEM - 01/04/2015 01:49:07 PM#
SYSTEM - 01/04/2015 02:05:21 PM#
SYSTEM - 01/04/2015 02:09:13 PM#
SYSTEM - 01/04/2015 02:09:38 PM#
SYSTEM - 01/04/2015 02:13:22 PM#
];
Regards,
Jagan.
if you only want the time part use frac (bold)
LOAD
TimeStamp,
timestamp(Timestamp#(subfield(TimeStamp,' - ',2),'DD/MM/YYYY hh:mm:ss TT#'), 'hh:mm') as DateTime_hhmm, // date and time hh:mm
time(frac(Timestamp#(subfield(TimeStamp,' - ',2),'DD/MM/YYYY hh:mm:ss TT#')), 'hh:mm') as Time_hhmm; // time hh:mm
LOAD * INLINE [
TimeStamp
SYSTEM - 01/04/2015 01:28:51 PM#
SYSTEM - 01/04/2015 01:49:07 PM#
SYSTEM - 01/04/2015 02:05:21 PM#
SYSTEM - 01/04/2015 02:09:13 PM#
SYSTEM - 01/04/2015 02:09:38 PM#
SYSTEM - 01/04/2015 02:13:22 PM#
];
Hi,
Please find the attached QVW file