SQLite Forum

Proposed slightly-incompatible change to date-time functions
One comment is that having Julian Day numbers as just an integer might not be as rare as you think, as if something is only storing the DATE something happened, and not a full Date/Time then that makes a lot of sense, and for historical data, that often makes sense as you may not really have information of the precision to add a time to the event.

Also, I think SQLite could take times that just happen to be exactly at noon, and thus equal to an integer (and of reasonable value) and store it in the database as an integer as a form of compression, so just because the column value happens to be stored as an integer, doesn't mean that an integer value was sent to the database, it could have been a real value that just happened to have no fractional bits present. I think this may be a function of the affinity of the column, but we don't know the affinity of existing legacy date columns

Maybe adding an 'Auto' modifier that makes the function decide format based on the type of the input would avoid the backwards compatibility break.