How QlikView calculates the number representation of a date
Article Number: 000002563 | Last Modified: 2018/06/12
QlikView uses 1899-12-30 as start date e.g. number representation 0.
This can cause some confusion if the number representation is used to interact with other software. For instance Excel uses 1900-01-00 while SQL Server uses 1900-01-01 as start date e.g. numeric representation 0.
QlikView starts with year 1899-12-30 as numeric representation of 0 while SQL Server starts with year 1900-01-01 as numeric representation of 0.
The reason why QlikView uses this odd start date is related to how Excel works. Excel starts with year 1900-01-00. But as Excel treats 1900 (incorrectly) as a leap year, QlikView has chosen to have 1899-12-30 as its start date. This means that QlikView is in sync with Excel from 1900-03-01.
Always use text representation of a date when querying other software to be sure to recieve expected result.
If there is a specific need to use the numeric date representation make sure to first verify start date for the other software before proceeding. For example, if you query SQL Server make sure to subtract with 2 to get in sync, otherwise the query might return wrong data.