Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Not applicable

How to strip off timestamp from a date field

Hi all,

I have a field in an Excel sheet that has date values stored in this way :

Accepted Date

September 20, 2016 10:31:55 AM GMT+05:30
October 14, 2016 8:16:52 PM GMT+05:30
November 21, 2016 7:05:30 PM GMT+05:30
November 23, 2016 6:40:42 PM GMT+05:30

However, I need to display the date in the 'mm/dd/yy' format in my chart, without the timestamp. What should I do?

Thanks in advance,

Kanchana

1 Solution

Accepted Solutions
Not applicable

Re: How to strip off timestamp from a date field

Try this

Date(Floor(Date(Date#(Left(AcceptedOn,Len(AcceptedOn)-10),'MMMM DD, YYYY hh:mm:ss TT'))),'DD/MM/YYYY')

17 Replies
kkkumar82
Valued Contributor III

Re: How to strip off timestamp from a date field

Use Date(Your field, 'MM/DD/YY') in your load script or in front end

if you remove time stamp part from your date field completely then use

Date(Floor(your field),'MM/DD/YY')

Not applicable

Re: How to strip off timestamp from a date field

Hi Kiran,

Thanks for your reply. I have tried using this expression in both the ways - in my load script or in my chart, but it does not work for some strange reason. I cannot figure why. Could it be that the field has to be formatted in a particular way in the Excel sheet itself?

Thanks,

Kanchana

kkkumar82
Valued Contributor III

Re: How to strip off timestamp from a date field

Can you post a sample excel with the date fields or your sample qvw

Partner
Partner

Re: How to strip off timestamp from a date field

Hi

If it is viable for you to remove GMT part of the field by subfield(FieldName,'GMT',1)  in script, then use expression as

=date(Date#(FieldName,'MMMM DD, YYYY hh:mm:ss TT'),'DD/MM/YY')

kalyanivintha1
Contributor II

Re: How to strip off timestamp from a date field

Hi,

i think this is helpful to you...

try this...

=(Date#(SubField(accepteddate,',',1) & Left(SubField(accepteddate,',',2),5),'MM-DD-YYYY'))

Re: How to strip off timestamp from a date field

Hi,

If it is coming as Date while loading the data in Qlik, you simply need to use below function.

Date(Floor(Fieldname),'MM/DD/YY') as NewDate

If it is coming as Text while loading the data in Qlik, then use below script.

Date(Floor(Date(Date#(Fieldname,'MMMM DD, YYYY hh:mm:ss TT'))),'MM/DD/YY') as NewDate


Regards,

Kaushik Solanki

Not applicable

Re: How to strip off timestamp from a date field

Can you send me the sample data, so that it would help to solve

Not applicable

Re: How to strip off timestamp from a date field

Hi Kalyani,

This works partially! There are 2 problems :

1. The formatting that you have doesn't work, this just strips off the timestamp and leaves the date behind, as-it-is.

2. The data associated with the older dates are lost.

i.e , if my table is as originally follows :

DateDefect ID
September 20, 2016 10:31:55 AM GMT+05:30MR00190080
October 14, 2016 8:16:52 PM GMT+05:30MR00190945
November 21, 2016 7:05:30 PM GMT+05:30MR00190946
November 23, 2016 6:40:42 PM GMT+05:30MR00190947
November 23, 2016 6:40:42 PM GMT+05:30MR00190948
October 14, 2016 8:16:52 PM GMT+05:30MR00190949

There are 2 defects logged on October 14. But now, with the timestamp being stripped off, when I plot, for example, Count([Defect ID]) against this new date as dimension, the values are all wrong.

Regards,

Kanchana

Not applicable

Re: How to strip off timestamp from a date field

Hi,

I have attached the sample Excel for you to look at. What I want is to strip off the time stamp from the date field and plot the number of Defect IDs on a particular date as a bar graph.

Regards,

Kanchana