Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Convert 12 Hour to 24 Hour Timestamp

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

9 Replies
jagan
Partner - Champion III
Partner - Champion III

Hi,

Try liek this

Load

*,

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

Hope this helps you.

Regards,

Jagan.

tresesco
MVP
MVP

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
MVP
MVP

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

Not applicable
Author

Hi

  

Submited ByCreatedDateTime
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#

];

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
MK_QSL
MVP
MVP

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

sasiparupudi1
Master III
Master III

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
Master III
Master III

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
Master III
Master III

Please close this thread if you are happy with the solution