Time and Date Confusion 1

Part 1 Formatting Functions

A common cause of issues on the QlikCommunity forum is date fields and the use of the formatting and interpretation functions.

First of all, let me state that there is no specific date data type in Qlikview. There are only two data types in Qlikview – text (or character) and number (or numeric). All number values are actually ‘duals’ – a dual has a text representation and an underlying numeric value. A date is simply a dual value with formatted text representation and an underlying value described below.

The integer portion of a date is the number of days since midnight of December 31 1899. So 1 January 1900 is day 1. July 7 2014 is day number 41827. This is identical to Excel and SQL Server as well as many other products.

The fractional portion of a date/time value represents the proportion of 1 day. So 41827.0 is time 00:00 on 7 July. 41827.25 is 6:00am (0.25 * 24). A pure date has a fractional part of 0. A pure time has only a fractional part (0.000000 – 0.9999999). 

An interval has both parts where the integer part represents days and the fractional part represents portions of a day, So 0.75 is an interval of 18 hours (0.75 * 24) or 1 080 minutes (0.75 * 24 * 60) and 1.5 is an intrerval of 25 hours.

One important point is that the value of the date dual is the underlying numeric, regardless of the chosen text representation.

So lets move on to the formatting functions:

• Date(numericvalue[, format])
• Time(numericvalue[, format])
• Timestamp(numericvalue[, format])
• Interval(numericvalue[, format])

Where numeric value is the date value and format is the optional format. If the formatted is omitted it will format the value to the default for your environment/locale.

These functions format only the text representation of the date/time dual. The underlying numeric value is not affected. So the text output of the following two expressions are identical, but the values are not equal:

                Date(‘2014/07/10 09:00:00’, ‘YYYY/MM/DD’)  = 41830.375
                Date(‘2017/07/10’, ‘YYYY/MM/DD’) = 41830.000

I have also often seen constructions such as Max(Date(someField)) or Date(someField)+2. The Date() format function is not required in these expressions, and only serves to clutter the expression, and in extreme cases may also affect performance.    

The most important points to take away from this are:

1. the value of the date is the numeric value of the dual and
2. the formatting functions only affect the text representation of  the date/time