Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
have to extract the hours from one field and the field is attached hear. but output is not comming for few records, i have used bellow formulas,
date(date#(left(mid([Date Time],6),19),'DD MMM YYYY HH:MM:SS'),'HH') AS Hour,
Timestamp(Timestamp#(left(mid([Date Time],6),19),'DD MMM YYYY HH:MM:SS'),'HH') AS hor,
The out come what i am getting is attached bellow.
Date Tame
Sat, 19 May 2018 11:09:46 +0100 |
Sat, 19 May 2018 09:09:46 -0400 (EDT) |
Sat, 19 May 2018 14:25:21 +0100 |
Sun, 20 May 2018 22:38:39 +0000 |
Mon, 21 May 2018 05:22:03 +0000 |
Mon, 21 May 2018 03:02:16 -0400 (EDT) |
Mon, 21 May 2018 07:06:04 +0000 |
Mon, 21 May 2018 07:22:44 +0000 |
Mon, 21 May 2018 07:55:21 +0000 |
Mon, 21 May 2018 08:24:42 +0000 |
Sat, 19 May 2018 16:27:29 +0000 |
Sat, 19 May 2018 17:37:01 +0100 |
Mon, 21 May 2018 08:52:39 +0100 |
Mon, 21 May 2018 08:49:44 +0000 |
Mon, 21 May 2018 08:57:30 +0000 |
Try this
Table:
LOAD *,
TimeStamp#(Left(SubField([Date Time], ', ', -1), 20), 'DD MMM YYYY hh:mm:ss') as TimeStamp,
Hour(TimeStamp#(Left(SubField([Date Time], ', ', -1), 20), 'DD MMM YYYY hh:mm:ss')) as Hour;
LOAD * INLINE [
Date Time
"Sat, 19 May 2018 11:09:46 +0100"
"Sat, 19 May 2018 09:09:46 -0400 (EDT)"
"Sat, 19 May 2018 14:25:21 +0100"
"Sun, 20 May 2018 22:38:39 +0000"
"Mon, 21 May 2018 05:22:03 +0000"
"Mon, 21 May 2018 03:02:16 -0400 (EDT)"
"Mon, 21 May 2018 07:06:04 +0000"
"Mon, 21 May 2018 07:22:44 +0000"
"Mon, 21 May 2018 07:55:21 +0000"
"Mon, 21 May 2018 08:24:42 +0000"
"Sat, 19 May 2018 16:27:29 +0000"
"Sat, 19 May 2018 17:37:01 +0100"
"Mon, 21 May 2018 08:52:39 +0100"
"Mon, 21 May 2018 08:49:44 +0000"
"Mon, 21 May 2018 08:57:30 +0000"
];
Hi Vinod,
why dont you take
=mid( Date Tame,18,19) as Hour
it will take hour directly.
Try this
Table:
LOAD *,
TimeStamp#(Left(SubField([Date Time], ', ', -1), 20), 'DD MMM YYYY hh:mm:ss') as TimeStamp,
Hour(TimeStamp#(Left(SubField([Date Time], ', ', -1), 20), 'DD MMM YYYY hh:mm:ss')) as Hour;
LOAD * INLINE [
Date Time
"Sat, 19 May 2018 11:09:46 +0100"
"Sat, 19 May 2018 09:09:46 -0400 (EDT)"
"Sat, 19 May 2018 14:25:21 +0100"
"Sun, 20 May 2018 22:38:39 +0000"
"Mon, 21 May 2018 05:22:03 +0000"
"Mon, 21 May 2018 03:02:16 -0400 (EDT)"
"Mon, 21 May 2018 07:06:04 +0000"
"Mon, 21 May 2018 07:22:44 +0000"
"Mon, 21 May 2018 07:55:21 +0000"
"Mon, 21 May 2018 08:24:42 +0000"
"Sat, 19 May 2018 16:27:29 +0000"
"Sat, 19 May 2018 17:37:01 +0100"
"Mon, 21 May 2018 08:52:39 +0100"
"Mon, 21 May 2018 08:49:44 +0000"
"Mon, 21 May 2018 08:57:30 +0000"
];
Sorry use this,
mid( Date Tame,18,2) as Hour