Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

miskin_m
Contributor

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

Community Browser