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.

[arulsettu]

Hi try this

Load *, Floor(DateField) as DateField

from tablename;

[#3]

Hi Manish,

Unfortunately this also returned an empty list box!

Please HELP!!!!!!!

[#4]

Hi, I already tried the floor function, it did not work! This seems so simple, why is it not working!?

[MK_QSL]

can you post your application?

If you can't, please post only your date field.

[#6]

hey sushant,

Try doing it with just Date() function for example

Date(datefield,'DD-MM-YYYY') as date1

it should work fine

regards

harshal

[#7]

Hi Manish,

PFA the excel. Just cant seem to get the data in the right format.

[#8]

Hi Harshal

Unfortunately, it did not work!

[tresesco]

I have used your sample file and it worked. PFA

[jonathandienst]

Hi

Do not use date(date#()) only - this reformats the date, but does NOT remove the time portion. You must use floor() or a subfield to remove the date:

This is what I suggest you need:

  date(floor(Date#(datefield, 'YYYY-MM-DD hh:mm:ss.ff')))

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

HTH

Jonathan

[#11]

Hi Tresesco,

Thanks, but i have a desktop version, cant open the file, can you tell me the specific function that you used?

By saying that it worked, you mean that you saw just one date in the list box,  as the sample data is just for 1 date?

Thanks for all your help.