convert to date format

[#1]

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.

[#2]

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]

is this?

a:

LOAD * INLINE [

    string

    20161020074931

];

LOAD *,

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

    Resident a;

[tresesco]

May be like:

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

[#5]

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]

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 · Accepted solution]

I am getting it right, see:

[#9]

Yes you are right.

Sorry did a small mistake.

Thank you.

[#10]

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)