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