topic Re: Convert 12 Hour to 24 Hour Timestamp in QlikView

Convert 12 Hour to 24 Hour Timestamp

Mon, 22 Jun 2015 08:17:04 GMT

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

Re: Convert 12 Hour to 24 Hour Timestamp

jagan — Mon, 22 Jun 2015 08:21:41 GMT

Hi,

Try liek this

Load

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

Hope this helps you.

Regards,

Jagan.

Re: Convert 12 Hour to 24 Hour Timestamp

tresB — Mon, 22 Jun 2015 08:22:25 GMT

In the script:

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

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

Re: Convert 12 Hour to 24 Hour Timestamp

MK_QSL — Mon, 22 Jun 2015 08:23:04 GMT

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

Re: Convert 12 Hour to 24 Hour Timestamp

Mon, 22 Jun 2015 09:27:00 GMT

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

Re: Convert 12 Hour to 24 Hour Timestamp

MayilVahanan — Mon, 22 Jun 2015 09:44:19 GMT

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#

];

Re: Convert 12 Hour to 24 Hour Timestamp

MK_QSL — Tue, 23 Jun 2015 05:54:27 GMT

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

Re: Convert 12 Hour to 24 Hour Timestamp

sasiparupudi1 — Tue, 23 Jun 2015 08:17:59 GMT

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

Re: Convert 12 Hour to 24 Hour Timestamp

sasiparupudi1 — Tue, 23 Jun 2015 08:37:56 GMT

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

Re: Convert 12 Hour to 24 Hour Timestamp

sasiparupudi1 — Wed, 24 Jun 2015 19:27:11 GMT

Please close this thread if you are happy with the solution