Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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
MVP
MVP

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

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

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)