Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

DateTime to Date

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

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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

View solution in original post

5 Replies
Anonymous
Not applicable
Author

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
Not applicable
Author

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

Not applicable
Author

Hi,

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

FLOOR(modificationdatetime) AS modificationdatetime


Hope this helps,

Matthias

johnw
Champion III
Champion III

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.

Not applicable
Author

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