Although not recommended, you can explicitly convert numbers to dates.
Analytics Database stores each DATE value as a four-byte integer using the following formula:
(year - 1900) * 10000 + (month * 100) + day
For example, December 31, 1985 is stored as the integer 851231; July 4, 1776 stored as -1239296; and March 30, 2041 stored as 1410330.
The following table demonstrates how numeric dates are interpreted when inserted into a column. The third date was likely intended to be 1990-12-01.
| Numeric Value | Translated Date Value |
|---|---|
| 901201 | 1990-12-01 |
| 1001201 | 2000-12-01 |
| 19901201 | 3890-12-01 |
This formula best fits two-digit dates in the 1900s. Because of the difficulty of using this format outside of the 1900s, dates are best specified as ANSI date literals instead.