Convert 12 Hour to 24 Hour Timestamp

[#1]

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

[jagan]

Hi,

Try liek this

Load

*,

TimeStamp([Created Date], 'MM/DD/YYYY hh:mm:ss') AS 24HourFormat

Hope this helps you.

Regards,

Jagan.

[tresesco]

In the script:

SET TimestampFormat='DD-MM-YYYY hh:mm:ss[.fff]';

TimeStamp(Timestamp#(YourDate, 'DD/MM/YYYY hh:mm:ss TT')) as NewDate

[MK_QSL]

TimeStamp(Timestamp#([Created Date],'DD/MM/YYYY hh:mm:ss TT'),'DD/MM/YYYY hh:mm:ss') as [Created Date]

[#5]

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

[MayilVahanan]

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#

];

[MK_QSL]

TimeStamp(Timestamp#(Trim(TextBetween([Submited By],'-','#')),'DD/MM/YYYY hh:mm:ss TT'),'DD/MM/YYYY hh:mm:ss') as [Submited By]

[sasiparupudi1]

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

[sasiparupudi1]

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

[sasiparupudi1]

Please close this thread if you are happy with the solution