Time and Date Confusion 2

Date conversion or interpretation functions

 Qlikview is able to automatically interpret dates that match te format set in your PC locale, or in the the Qlikview environment variables (usually automatically added to the start of the script in a new document)

Set TimeFormat='hh:mm:ss';
Set DateFormat='YYYY/MM/DD';
Set TimestampFormat='YYYY/MM/DD hh:mm:ss[.fff]';

However, the date and time values in your source systems are frequently in a different format. This is where the date interpretation functions come in:

• Date#(stringvalue[, format])
• Time#(stringvalue[, format])
• Timestamp#(stringvalue[, format])
• Interval#(stringvalue[, format]) 

The functions ensure that the date field is correctly interpreted. So lets assume that the field is in YYYYMMDD format (eg in a load statement):

    Date#(SourceField, ‘YYYYMMDD’) As DateField,

The interpretation functions only affect the numeric value of the dual. The above expression will return the correct numeric value, but the text format will still be YYYYMMDD format, as this what Qlikview inferred as the correct format from the first record. If you would like to corrrectly interpret the date and format it on the default format for you environment/locale, them combine the interpretation function with the relevamt formatting function, like this:

     Date(Date#(‘YYYYMMDD’)) As DateField,
     Date(Date#(‘YYYYMMDD’), ‘D MMM YYYY’) As DateField2,  

The first will produce a date formatted in the default date format and the second in D MMM YYYY format.

Hopefully this post and the previous one helps you to  understand how to use the date/time format and interpretation functions.

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