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: 
markp201
Creator III
Creator III

String to date/timestamp...

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?

1 Solution

Accepted Solutions
markp201
Creator III
Creator III
Author

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.

View solution in original post

6 Replies
el_aprendiz111
Specialist
Specialist

Hi,

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

markp201
Creator III
Creator III
Author

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Not applicable

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
Creator III
Creator III
Author

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.