The Date Function

[hic]

One Qlik function that occasionally causes confusion is the Date function. I have often seen errors caused by an incorrect usage of it, so today I will try to explain what the function does – and what it does not.

[hic]

Interpretation vs Formatting

The first thing you should be aware of is that there are two different functions: Date#() and Date(). The first is an Interpretation function and the second is a Formatting function.

• Interpretation functions use the textual value of the input, and convert this to a number.
• Formatting functions use the numeric value of the input, and convert this to a text.

In both cases, the output is a dual, i.e. it has both a textual value and a numeric value. The textual value is displayed, whereas the numeric value is used for all numerical calculations and sorting.

The table below shows how to use the interpretation function Date#(). Note that the format code must match the input parameter.

This is very different from the formatting function Date(). Next table shows how to use this function. Note that the format code matches the format of the output text.

In real life, it is often useful to nest an interpretation function inside a formatting function:

Formatting vs Rounding

The second thing you should be aware of is that the Date() function and other formatting functions never change the numeric value of the input value.

This means that you can format a timestamp as a date only, without the time information. This can sometimes be confusing since there is a “hidden” value. In the table below, you can see that the input value corresponds to 12:00 in the middle of the day, but the Date() function effectively hides this from the textual output - but it remains in the the numeric value.

So what should you do if you want to remove the time part of the field, and just keep the date part? Well, obviously you must use a function that changes the numeric value: You need a Rounding function, e.g. DayStart() or Floor().

In the table below, you can compare the output of the Date() function with a couple of different rounding and formatting options.

Summary

The above discussion is not relevant to dates only. It is just as relevant for Years, Weeks, hours, seconds and any other time interval. Further, it is relevant to a number of other functions:

Interpretation functions: Date#(), TimeStamp#(), Time#(), Interval#(), etc.

Formatting functions: Date(), TimeStamp(), Time(), Interval(), etc.

Rounding functions: Round(), Floor(), Ceil(), DayStart(), WeekStart(), MonthStart(), etc.

Combine these functions sensibly, and you will be able to round or format any way you want.

HIC

Further reading related to this topic:

Get the Dates Right

Why don’t my dates work?

[#3]

Henric,

Nice Post..

Mohan

[robert99]

Thanks Henric

Dates completely confused me when I first started using QV. I eventually found solutions but by trial and error. Now I finally understand (and I read all your previous blog posts on dates).

[dmohanty]

A crystal clear blog. Very well explained!

[#6]

Thanks for the post ,

Regards,

Mahesh

[#7]

thank u

[#8]

How to Multiply Previous Rows in a Table

Hi, Could you help on this Problem? Thank you very much!

[#9]

1. "... , and convert this to a number.".

but

"...the output is a dual, i.e. it has both a textual value and a numeric value".

2. "..., and convert this to a text".

but

"...the output is a dual, i.e. it has both a textual value and a numeric value".

???

I can use it but I am not sure that I understand the layers here.

[hic]

Both functions are duals, i.e. they both return a textual and a numeric value.

The textual input of the Date#() function will be used as textual output. In addition, the numeric interpretation of the text will be used as numeric value.

The numeric input of the Date() function will be used as numeric output. In addition, the textual formatting of the number will be used as textual value.

HIC

[#11]

thx, but there must like a "master and slave" concept in this. For instance what does the table viewer tag tells us, so for '#' the numric value becomes the "master"? Though still it is dual...   

[hic]

You could say that for the Date#() function, the textual input is the master; and for the Date() function, the numeric input is the master.

In the Date#() function, the textual input is used (as-is) as output, and the numeric output is created (transformed) from the textual input. And the opposite is true for the Date() function.

HIC