SQLite Forum

Proposed slightly-incompatible change to date-time functions
As others have said, many applications store JDNs (Julian Day Numbers) as INT.

In many use cases dates are best expressed in whole days, e.g. accounting. For instance I've almost never seen an invoice with h:m:s timestamps. In history only whole days are significant and you expect sign of friendship on your "integral" birthday no matter the clock hour.

While I fully understand the reason for the proposed change, I suspect this would break a significant number of real-world applications.

An SQLite JDN is currently Julian date compatible but would no more yield a valid result if the change was silently applied. By "silently" I mean that its highly probable that latest SQLite releases are often used 'as is' (as amalgamation or directly as a downloaded .DLL) without reading the release notes in full. This thanks to the high confidence people have built wrt SQLite code. People know by experience that subsequent SQLite releases are never worse than previous, fixing obscure bugs, improving speed or adding new features.

Application code (whatever language they are built with) or libraries often offer routines to deal with JDNs or JDN differences.

The perspective of a number of sqlite3.dll hanging around on the average Windows storage for instance, some speaking JDN, some speaking epoch, is quite embarrasing. From my point of view, this would be a breach of contract.