Date# Function vs. Date Function

[Jennell_McIntire]

What is the difference between the Date# function and the Date function? These two functions seem quite similar, but they have different purposes. It took me a while to understand when to use one over the other. Now that I have a handle on it, I thought I would share what I have learned. The Date# function is an interpretation function. According to Qlik Help, “Date# evaluates an expression as a date in the format specified in the second argument, if supplied.” I use the Date# function when I am loading a value that I want to be perceived as a date. For example, in the partial script below, I loaded “YR” from an Excel file and to ensure that the value was evaluated as a date with the year format ‘YYYY’, I used the Date# function.

The syntax of the Date# function is as follows:

Date#(text[, format])

The format parameter of the Date# and Date functions is optional. If it is not included in the expression, it uses the date format set in the system variables in the script. Using Date#() is an important step if the “Year” field is used later in the script to join data or to compare to data that has a date value. When evaluating 2 values, you want to ensure that they are formatted the same and that you are comparing apples to apples.

The Date function is a formatting function. According to Qlik Help, “Date() formats an expression as a date using the format set in the system variables in the data load script, or the operating system, or a format string, if supplied.” I use the Date function to format a date a specific way. For example, I may format a date as ‘YYYY’ if I only am interested in seeing the year. I could also format the date like this ‘M/D/YYYY’ to see the month, day and year.

The syntax for the Date function is as follows:

Date(text[, format])

In the example below, I am formatting the “Yr” field as a 4-digit year (i.e. 2021).

I can also format a date as seen below. This expression will return 09/17/2021.

On occasion, I have used both the Date# and the Date functions in the same expression. For example, if I am loading text with the format YYYYMM and I want to format it as MMM-YYYY, I cannot simply use the expression Date(text, ‘MMM-YYYY’) because it does not pick up that the text is a date. So, I need to first interpret the text as a date and then I can format it. This expression works:

Date(Date#(text, ‘YYYYMM’), ‘MMM-YYYY’)

The Date# function first identifies the text as a date and indicates the format the date is in (‘YYYYMM’). Then the Date function formats the text like this ‘MMM-YYYY’.

Both the Date# and Date functions can be used in script and chart functions. Date#() interprets the data as a date and Date() formats the date as specified. I hope this was helpful.

Thanks,

Jennell

[Vegar]

It's great that you raise the awareness of the difference between these two functions.  I have seen many example of mixup between them.

When working with format functions like Date() I always  pont out that the underlying numeric values are not affected by using them.

Both Date(today()) and Date(now()) will return the same string value, but they still holds two different numeric values, the second will hold the timefraction as well. 

date#() on the other hand will return the integer value of the text representation. 

[pover]

I also tend to forget that when doing the Date(Date#()) combo that the inner date should not be more detailed then the outer format.  For example,

Date(Date#(text, ‘YYYYMMDD’), ‘MMM-YYYY’)

will return what appears to be duplicate dimension and filter values in the UI because they have different underlying integer values. 

So, any time you lower the level of detail, you should use an additional function like Floor() to go from timestamp to days or MonthName() to go from days to months.

Date(MonthName(Date#(text, ‘YYYYMMDD’)), ‘MMM-YYYY’)

[stevedark]

Hi @Jennell_McIntire 

Thanks for sharing this! It never hurts to remind people of the difference. When I run my Sense training course I often have QlikView developers who are looking to cross-train to Sense, and it never ceases to amaze me how many people don't fully appreciate why they use both functions.

In the same section of the course I also go into how times are decimals, and you need to bear this in mind when building apps, so I explain that 6pm today will be today()+0.75.

Once you appreciate this you can do funky stuff like rounding times to the nearest five minutes by doing date(round([Time Updated], (1/1440)*5), 'DD MMM YYYY hh:mm').

This can make data sets much smaller and filter panes less laborious to scroll through.

Steve

[ssssssss88888888]

this is amazing and very well constructed and easy to understand. thank you, i really appreciate it!!!

[ab9503]

Thanks for that helpful article! I would like to clarify two points, or check if I've got the wrong understanding:

1. For the Date() function, I think that the first argument should be of type Number instead of Text. (Though I also understand that Qlik Sense is generally forgiving when it comes to data types.)

2. I think the fieldname "Yr" may not require quotes in the expression. Could you confirm this point?

Thank you again.

[CJ_Bauder]

@ab9503  You are right, fields without spaces or special chars do not require "" or [].

The "text" in the second Date() function just represents the field name text, but you are right - this should be a number or dual value with a numeric value.

Happy coding! 

[ab9503]

@CJ_Bauder , oh, I forgot that double-quotes can be used like square brackets (i.e., required when the fieldname contains spaces, optional otherwise). Thanks!