How QlikView calculates the number representation of a date
Article Number: 000002563 | Last Modified: 2018/06/12
Description
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.
Cause
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.
Resolution
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.
Get Answers
Find Answers
Qlik Community
Collaborate with over 60,000 Qlik technologists and members around the world to get answers to your questions, and maximize success.
Experiencing a serious issue, please contact us by phone. For Data Integration related issues please refer to your onboarding documentation for current phone number.