DateTime to Date

[Anonymous]

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??

[Anonymous · Accepted solution]

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

[Anonymous]

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

[#4]

Hi,

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

FLOOR(modificationdatetime) AS modificationdatetime

Hope this helps,

Matthias

[johnw]

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.

[#6]

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