topic DateTime to Date in QlikView

DateTime to Date

Anonymous — Tue, 02 Mar 2010 14:42:07 GMT

Hi all,

Think this shouldn't be a difficult one ... but just can't find the correct answer ...

I have a load scipt containing a DateTime value

LOAD .... modificationdatetime, ... FROM ...

I also have a calendar to which I want the modificationdatetime point to ... Sounds easy?


LET vMinDate = NUM(DATE('1-1-2004'));
LET vMaxDate = NUM(TODAY());

TempCalendar:
LOAD
 $(vMinDate)+ITERNO() AS NUM,
 DATE($(vMinDate)+ITERNO()-1) AS TempDate

AUTOGENERATE (1) WHILE
 $(vMinDate)+ITERNO()-1 <= $(vMaxDate);

ModificationDateCalendar:
LOAD
 TempDate AS modificationdatetime,
 YEAR(TempDate) AS Year,
 APPLYMAP('Quarter', NUM(MONTH(TempDate))) AS Quarter,
 MONTH(TempDate) AS Month,
 WEEK(TempDate) AS Week,
 WEEKDAY(TempDate) AS WeekDay,
 DAY(TempDate) AS Day,
 DATE(MONTHSTART(TempDate), 'MMM-YYYY') AS MonthYear

RESIDENT
 TempCalendar
ORDER BY
 TempDate;
DROP TABLE TempCalendar;

Does not seem to be wrong ...

But now a way to find to get the correct date connection.

Did try the following

date(modificationdatetime)
date(modificationdatetime, 'DD-MM-YYYY')
date(num(modifcationdatetime))

In all these situations the date is displayed correctly, but the connection with the calendar seems to be wrong, since I don't see anything when selection the Year or something.

Any suggestions??

DateTime to Date

Anonymous — Tue, 02 Mar 2010 14:52:48 GMT

Date function returns only format, but does not change the value.

You need to use MakeDate function like this:

MakeDate(Year(modificationdatetime),Month(modificationdatetime),Day(modificationdatetime))

Rds,
AT

DateTime to Date

Anonymous — Tue, 02 Mar 2010 15:07:30 GMT

thx ... it was really easy ... just couldn't figure it out

DateTime to Date

Tue, 02 Mar 2010 15:25:22 GMT

Hi,

If you have a Timestamp you need to get the date isolated:

FLOOR(modificationdatetime) AS modificationdatetime

Hope this helps,

Matthias

DateTime to Date

johnw — Thu, 04 Mar 2010 01:28:02 GMT

I tend to use floor() myself, but if I weren't already in that habit, the daystart() function would probably be a slightly better choice. Same exact numeric result, but floor() is a mathematical function that takes advantage of QlikView's internal formats, while daystart() is a date/time function that doesn't depend on the internal format. Very minor point overall, though, as I'm pretty confident that QlikView will never change the way they represent dates and times.

Re: DateTime to Date

Tue, 21 Jun 2016 08:35:10 GMT

Hi Matthias, Your answer is so much helpful. Thank You.