topic Re: String to date/timestamp... in QlikView

String to date/timestamp...

markp201 — Mon, 08 May 2017 20:51:05 GMT

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?

Re: String to date/timestamp...

el_aprendiz111 — Mon, 08 May 2017 21:19:19 GMT

Hi,

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

Re: String to date/timestamp...

markp201 — Mon, 08 May 2017 22:04:16 GMT

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

Still getting null.

Re: String to date/timestamp...

Digvijay_Singh — Mon, 08 May 2017 23:52:22 GMT

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" ];

Re: String to date/timestamp...

Anil_Babu_Samineni — Tue, 09 May 2017 00:18:27 GMT

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')

Re: String to date/timestamp...

Tue, 09 May 2017 03:01:14 GMT

Hi, how are you?

Could you try this?

=Date(

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

,'MM/YYYY')

See you latter,

Christyan Almeida

Re: String to date/timestamp...

markp201 — Wed, 10 May 2017 11:02:55 GMT

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.