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]

try this

date(date#(datefield,'YYYY-MM-DD hh:mm:ss'),'DD-MM-YYYYY')

[datanibbler]

Hi,

so you want only the date in QlikView, but in your base_data you have multiple entries per date?

That cannot be done easily - well, you could cut off the time_part using a STRING function, but that would cause you to lose data, wouldn't it?

=> If you don't need multiple records per day, but only one, try using FIRST() or SUBSTRING().

[#4]

Trying now. Will keep you posted.

[#5]

Hi Datanibbler,

There is only loss of the time part of the data, there are not multiple dates in each cell, just a date and a time.

[#6]

Hi,

You can use these;

subfield(F1,' ',1) as date1,
or
date#(left(F1,10),'YYYY-MM-DD') as date

HTH

-Shruti

[sushil353]

Hi..

first of all set the environment variable to the desired format of date

SET DateFormat='MM/DD/YYYY';

after that you can try this expression

date(date#(datefield,'YYYY-MM-DD hh:mm:ss'),'MM/DD/YYYY')

HTH

Sushil

[MK_QSL]

Add Below Lines to your script, it will completely remove the time field....

Date(Floor(YourDateField,'YYYY-MM-DD')

[jagannalla]

Hi,

Try this,

Date(Floor(DateField),'MM/DD/YYY'Y)

Hope it helps you!!!

Thanks,

Jagan

[#10]

Hi Sushil,

This returned a list box which was empty