Creation datetime ==> date

[#1]

I have lists of customer purchasing information. and I am struggling with purchasing time column.

my column looks like YYYY-MM-DD hh:mm:ss.

I have used date function and I am able to trim out time part and I only have YYYY-MM-DD now.

so the list box that I am seeing looks like this

num  name     date

1      david      2016-02-01

2     hannah    2016-02-01

3     daniel     2016-02-01

4     tanya      2016-02-01

...

...

...

each column is a list box. I guess this makes sense since only the time part was taken out.

but what I want to do is to be able to only see one date by each row. and by clicking each row, I want to be able to filter out all the customers that purchased on that day.

I want my date listbox to look like

2016-02-01

2016-02-02

2016-02-03

...

and another question is that I want to create a bar chart that shows how many records are created in one day.

but right now when I set the dimension as date, multiple 2016-02-01 are on horizontal axis.

Thank you in advance!!

[Gysbert_Wassenaar]

Using the Date() function does not cut off the time part. It only changes the display format, not the value. You need to use the Floor() function if you want to remove the time part: Date(Floor(MyDate),'MM/DD/YYYY')

[alexdataiq]

How are you trimming the date part of your timestamp? Are you doing in from the script or in the Front-End.

Anyway, have you trying using Floor(Field)? that way QV will only keep the Date part of the timestamp. e.g:

LOAD

    num,

    name,

    Floor(Date) as newDate

FROM....

Could you provide an example of your application?

Regards

[kaushiknsolanki]

Hi,

It seems that your date column is coming as text instead of Date.

Try below code while loading data.

Date(Timestamp(Timestamp#(FieldDate,'YYYY-DD-MM hh:mm:ss')),'YYYY-DD-MM') as Date

Regards,

Kaushik Solanki

[#5]

thank you it worked!!!

[#6]

thank you it worked!!!

[#7]

Hi Kaushik,

Thank you so much for your answer.

I want to know more about what exactly Timestamp means and what you mean by date column is coming as text not as date.

Please explain little more I want to know what function the Timestamp has.

and further more, when organization uses shared Date QVD, do they make sure that all values are in there as date values not as text? this is very new to me.

I am asking this since I am curious if the two columns do not merge properly if one is text based and one is date value based even though their values look identical on the surface.

thank you!!

[kaushiknsolanki]

Hi David,

What I thought is, Many a times when your data source is flat files or excel, the Date Field doesn't get recognized as Date field, instead it is recognized as text column. So to convert such text into date, we use the #functions.

So in the expression I gave, I tried to convert the timestamp into a timestamp format first and then from that timestamp I tried to get date using Date function.

Regards,

Kaushik Solanki