topic Re: convert to date format in QlikView

convert to date format

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: convert to date format

Fri, 25 Nov 2016 05:57:15 GMT

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

Re: convert to date format

Chanty4u — Fri, 25 Nov 2016 05:57:32 GMT

is this?

LOAD * INLINE [

string

20161020074931

];

LOAD *,

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

Resident a;

Re: convert to date format

tresB — Fri, 25 Nov 2016 06:04:27 GMT

May be like:

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

Re: convert to date format

Fri, 25 Nov 2016 06:19:31 GMT

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 .

Re: convert to date format

eromiyasimon — Fri, 25 Nov 2016 06:31:01 GMT

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

Re: convert to date format

tamilarasu — Fri, 25 Nov 2016 06:31:32 GMT

Hi Balakrishnan,

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

Re: convert to date format

tresB — Fri, 25 Nov 2016 06:32:28 GMT

I am getting it right, see:

Re: convert to date format

Fri, 25 Nov 2016 06:36:14 GMT

Yes you are right.

Sorry did a small mistake.

Thank you.

Re: convert to date format

Fri, 25 Nov 2016 06:37:24 GMT

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)

Re: convert to date format

Fri, 25 Nov 2016 06:40:02 GMT

Hi Plz look into attachment.