Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Jun 14, 2022 8:39:53 AM
Jan 25, 2016 1:17:51 PM
Date formats can often be challenging for beginners, particularly when different data sources supply dates in different formats, and your reporting requirements specify yet another final date format.
There are two main functions for dealing with date formats.
Often I see people using the two functions in different combinations with a bit of trial and error until they get the result they want, without really understanding why it has worked. But, with a few simple basics understood, this is easy to understand.
Date fields, once properly formatted, are a dual data type. This means that each value in the field has two components, a string (text) representation of the date, and an underlying numeric component.
You may have seen when working with dates in Microsoft Excel that sometimes when you change the format of a date cell you end up with 42019 instead of 15/01/2015. This number is a difference, in days, between the date and an “epoch date”, in this case 01/01/1900. This is done, because this way it’s much easier to subtract one date from another to find the difference or add a number of days, months or years to a date to forecast for example.
Date#() takes a date string (e.g. the text ‘15/01/2015’) and calculates and adds the numeric component to make the proper dual data type date field. It leaves the string component unchanged.
Date() takes a number (either the number 42019 or an already properly interpreted date field with the underlying numeric component already in place) and calculates and adds (or changes) the string component. It leaves the numeric component unchanged.
Both functions take an optional second parameter as a format string for how to interpret in the case of Date() or how to format in the case of Date#(). If this second parameter is not provided, the default set in the script variables is assumed.
Let's assume the source field is called [OrderDate].
Reading from the inside out, we take the [OrderDate] field, apply Date#() to interpret the string and calculate the appropriate numerical component using the format mask YYYY-MM-DD, and then apply Date() to that result to reformat the string representation using the format mask DD/MM/YYYY.
Times and Timestamps take this simple concept one step further. In this case a time is again represented by a string (e.g. '12:00:00' or '15/01/2015 12:00:00') and the underlying numerical component will represent the time as a decimal fraction of a day (e.g. 0.5 or 42019.5). The corresponding functions are Time() and Time#() or Timestamp() and Timestamp#().
This is really helpful, thanks a million.
Is the "epoch date" always set to '01/01/1900' in qlikview?
The epoch base in Qlik is Dec 30,1899; that is day 0. Day 1 is Dec 31, 1899.
Note that in XLS date 1 is January 1st 1900. Date 60 in XLS is February 29th 1900, a date that does not exist. Starting date 61 XLS and Qlik have the same number for the same dates. See https://support.microsoft.com/en-us/help/214326/excel-incorrectly-assumes-that-the-year-1900-is-a-le...