Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
Created Date |
01/04/2015 01:03:40 PM |
01/04/2015 01:13:27 PM |
01/04/2015 01:28:51 PM |
01/04/2015 01:37:25 PM |
01/04/2015 01:46:16 PM |
01/04/2015 01:49:07 PM |
01/04/2015 02:09:38 PM |
01/04/2015 02:17:16 PM |
01/04/2015 02:29:51 PM |
01/04/2015 02:57:01 PM |
01/04/2015 03:10:29 PM |
I would i convert the above into 24 Hours Time Format.
Regards,
Senthil
Hi,
Try liek this
Load
*,
TimeStamp([Created Date], 'MM/DD/YYYY hh:mm:ss') AS 24HourFormat
Hope this helps you.
Regards,
Jagan.
In the script:
SET TimestampFormat='DD-MM-YYYY hh:mm:ss[.fff]';
TimeStamp(Timestamp#(YourDate, 'DD/MM/YYYY hh:mm:ss TT')) as NewDate
TimeStamp(Timestamp#([Created Date],'DD/MM/YYYY hh:mm:ss TT'),'DD/MM/YYYY hh:mm:ss') as [Created Date]
Hi
Submited By | CreatedDateTime |
1270391: S,Sreenath - 09/04/2015 11:42:16 PM# | 09/04/2015 11:42:16 PM |
SYSTEM - 01/04/2015 01:03:40 PM# | 01/04/2015 01:03:40 PM |
SYSTEM - 01/04/2015 01:13:27 PM# | 01/04/2015 01:13:27 PM |
SYSTEM - 01/04/2015 01:28:51 PM# | 01/04/2015 01:28:51 PM |
SYSTEM - 01/04/2015 01:37:25 PM# | 01/04/2015 01:37:25 PM |
SYSTEM - 01/04/2015 01:46:16 PM# | 01/04/2015 01:46:16 PM |
SYSTEM - 01/04/2015 01:49:07 PM# | 01/04/2015 01:49:07 PM |
Submited by is the Source data. From the source data am creating createdDateTime by using
Left([Submited By],7)as Created_ID,
time#(TextBetween([Submited By],'-','#'),'YYYY-MM-DD hh:mm:ss')as CreatedDateTime,
Now i have to convert the submited by to 24 Hours Time format.
Thanks
Hi
Try like this
LOAD *, Timestamp(Timestamp#(Trim(TextBetween([Submited By],'-','#')),'DD/MM/YYYY hh:mm:ss TT'),'DD/MM/YYYY hh:mm:ss') as Time INLINE [
Submited By
"1270391: S,Sreenath - 09/04/2015 11:42:16 PM#"
SYSTEM - 01/04/2015 01:03:40 PM#
SYSTEM - 01/04/2015 01:13:27 PM#
SYSTEM - 01/04/2015 01:28:51 PM#
SYSTEM - 01/04/2015 01:37:25 PM#
SYSTEM - 01/04/2015 01:46:16 PM#
SYSTEM - 01/04/2015 01:49:07 PM#
];
TimeStamp(Timestamp#(Trim(TextBetween([Submited By],'-','#')),'DD/MM/YYYY hh:mm:ss TT'),'DD/MM/YYYY hh:mm:ss') as [Submited By]
Timestamp#(Date(Date#(trim(subfield(trim(subfield([Submited By],'-',2)),' ',1)),'DD/MM/YYYY'),'YYYY/MM/DD')& ' ' &Time(Time#(trim(subfield(trim(subfield([Submited By],'-',2)),' ',2)) &' '& Replace(trim(subfield(trim(subfield([Submited By],'-',2)),' ',3)),'#',''),'hh:mm:ss TT'),'hh:mm:ss'),'YYYY/MM/DD hh:mm:ss') as timestamp
or
timestamp(timestamp#(trim(subfield(trim(subfield([Submited By],'-',2)),'#',1)),'DD/MM/YYYY hh:mm:ss TT'),'YYYY/MM/DD hh:mm:ss') as TimeStamp
Please close this thread if you are happy with the solution