String to date/timestamp...

[markp201]

I have the following which qlik is interpreting as string

1/1/2016 1:00:18PM

1/1/2016 1:02:33PM

I need to reformat as MM/YY

=timestamp#(cr_timestamp,'M/D/YYYY h:mm:sstt') displays the same as the original

1/1/2016 1:00:18PM

1/1/2016 1:02:33PM

=date(timestamp#(cr_timestamp,'M/D/YYYY h:mm:ssTT'),'MM/YYYY') displays null.

The date# function works but as soon as I apply any formatting, it return null

There is one row which has "timestamp" in the field (including the double-quotes).  Is this the problem?

[el_aprendiz111]

Hi,

=Date(Date#('1/1/2016 1:00:18PM','DD/MM/YYYY h:mm:sstt'),'MM/YYYY')

[markp201]

Your constant expression works but this is a field in the table.

Still getting null.

[Digvijay_Singh]

Try this script, it generates right format in front end -

Load Date(floor(Timestamp#(Date,'M/D/YYYY h:mm:sstt')),'MM/YYYY') as Date inline [

Date

1/1/2016 1:00:18PM

1/1/2016 1:02:33PM

"2/1/2016 1:02:33PM" ];

[Anil_Babu_Samineni]

Try this as well

1) MonthName(Timestamp#('1/1/2016 1:00:18PM','D/M/YYYY hh:mm:ssTT'))

2) Date(Timestamp#('1/1/2016 1:00:18PM','D/M/YYYY hh:mm:ssTT'),'MM/YYYY')

[#6]

Hi, how are you?

Could you try this?

=Date(

          Date#(DateTimeField,'DD/MM/YYYY h:mm:sstt')

,'MM/YYYY')

See you latter,

Christyan Almeida

[markp201 · Accepted solution]

Thanks to all for replies.  They were quite helpful.

All the following 1/1/2016 which seems odd

LEFT('1/1/2016 5:00:00PM',10) should be 1/1/2016 5

LEFT('1/10/2016 5:00:00PM',10) ok

LEFT('10/10/2016 5:00:00PM',10) ok

Any # function would be ok but any formatting would result in null.

What worked was to use LEFT(field,10) for the date and RIGHT(field,10) for the time.

I've used datetime stamps before but suspect this had some non-display characters.