Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

convert to date format

Hi

Please convert the following string into a date and time format like this 'yyyy-mm-dd hh:mm:s'

The input string is '20161020T074931.000-0500'.

Here 2016- yyyy,

10-mm,

20-dd,

07- hh

49- mm

31- ss.

1 Solution

Accepted Solutions
tresesco
MVP
MVP

I am getting it right, see:

Capture.PNG

View solution in original post

10 Replies
Not applicable
Author

Hi,

Pls try :

Variable1  = '20161020T074931.000-0500'

=Timestamp(

Timestamp#(

SubField(SubField(Variable1,'.',1),'T',1) &Chr(32) & SubField(SubField(Variable1,'.',1),'T',-1)

,'YYYYMMDD hhmmss')

, 'yyyy-MM-dd hh:mm:s')

Regards,

Srashti

Chanty4u
Champion III
Champion III

is this?

a:

LOAD * INLINE [

    string

    20161020074931

];

LOAD *,

Date(Date#(string,'YYYYMMDDhhmmss'),'yyyy-mm-dd hh:mm:s') as new

    Resident a;sting.PNG

tresesco
MVP
MVP

May be like:

=Date(Date#(PurgeChar( '20161020T074931.000-0500','T'), 'YYYYMMDDhhmmss.fff-ffff'))

Not applicable
Author

Hi Tresesco,

Thanks for the reply.

But I am getting the wrong minute value.

here the time is 07:49:31

but the result i got when i used your exp is 07:10:31, Also the minute is shown as 10 for all the input .

eromiyasimon
Creator II
Creator II

Set up the timestamp date format to 'YYYY/MM/DD hh:mm:ss[.fff]'; in Edit script

then use the below expression

=Timestamp(Timestamp#(SubField(SubField('20161020T074931.000-0500','.',1),'T',1) &Chr(32) & SubField(SubField('20161020T074931.000-0500','.',1),'T',-1),'YYYYMMDD hhmmss'), 'yyyy-MM-dd hh:mm:s')

this will workout

go through attached QVW

tamilarasu

Hi Balakrishnan,

=Date(Date#(Subfield(Replace('20161020T074931.000-0500','T',''),'.',1),'YYYYMMDDhhmmss'),'YYYY-MM-DD hh:mm:ss')

tresesco
MVP
MVP

I am getting it right, see:

Capture.PNG

View solution in original post

Not applicable
Author

Yes you are right.

Sorry did a small mistake.

Thank you.

Not applicable
Author

Hi Try this

=Date(Date#(Left('20161020T074931.000-0500',8),'YYYYMMDD'),'YYYY-MM-DD')&' '&Left(Right(SubField('20161020T074931.000-0500','.',1),6),2)&':'&Mid(Right(SubField('20161020T074931.000-0500','.',1),6),3,2)&':'&Right(Right(SubField('20161020T074931.000-0500','.',1),6),2)