How to get date properly

[#1]

Hi Everyone!

I am getting data onto Qlikview from excel. In it is a column for date, but its formatted in a way that both date and time are present in the cell, for example:

2014-01-19 23:59:33.0

Now, from it I only need date, not the time. I tried using the date function, but when i use it as a list box, the same date turns up multiple times( as many times as its entries in the excel)

I need the date as unique values in a list, which function can i use?

Thanks in advance for all the queries.

[#2]

Hi Jonathan,

Instead of date(floor(Date#('2014-01-19 23:59:33.0', 'YYYY-MM-DD hh:mm:ss.ff')))

can I use

date(floor(Date#('2014-01-19 23:59:33.0', 'YYYY-MM-DD')))

so i get only the date and not the timestamp?

[tresesco · Accepted solution]

LOAD Date(Floor([Request Time])) as Date

FROM

.....\sample data for date.xlsx]

(ooxml, embedded labels, table is Sheet1);

[#4]

Hi Jonathan,

I just read what i replied to you and realized it was pretty stupid.

I'll try your method.

[jonathandienst]

P Sushant wrote:

Instead of date(floor(Date#('2014-01-19 23:59:33.0', 'YYYY-MM-DD hh:mm:ss.ff')))

can I use

date(floor(Date#('2014-01-19 23:59:33.0', 'YYYY-MM-DD')))

so i get only the date and not the timestamp?

No - the date# must match the format string. The floor() removes the time portion.

     date#() - recognise the string as a date and convert it

     floor() discard the fractional portion leaving integer days

     date() format the output from floor using the default date format in your model

HTH

Jonathan

[#6]

Ladies and gentlemen , we have a WINNER!!!!!

Tresesco is the one who tackled the problem and WON! Thanks a LOT!!!!

Thank you Everyone for all the help, you guys are awesome!!!!