On 2016/05/05 4:26 AM, Cecil Westerhof wrote:
> The statement:
> SELECT strftime('%Y-%m-%d %H:%M:%f', '2016-04-31 17:19:59.670')
> gives:
> 2016-04-31 17:19:59.670
>
> Should that not be NULL?
>
> It does with:
> SELECT strftime('%Y-%m-%d %H:%M:%f', '2016-04-32 17:19:59.670')
>
> It looks like a value of 31 is always allowed for day:
> SELECT strftime('%Y-%m-%d %H:%M:%f', '2016-02-31 17:19:59.670')
> gives:
> 2016-02-31 17:19:59.670
>
Yes - Keith's suggestion would work best if you want to check whether
the STRING you check is a valid Date-Time STRING, that's very different
from checking whether it is a valid actual Date and Time. The Notion of
checking valid dates is a lot more complicated, and as Simon has pointed
out, should rather be done in your code since you can then decide to
what level you wish to check.
To name a few idiosyncrasies - Sometimes on a year with a leap-second,
the date-string '2004-12-31 23:59:60' is an actual valid date-time,
while the very next year, that would not be valid seconds. Leap years
themselves also have problems - the easiest check is to see if the year
is divisible by 4 and then allow a 29th on Feb, but of course for the
year 1900 this would have been wrong, but for 2000 this is right again,
etc. Then there starts the problem of confusing whole days when getting
close to the Julian/Gregorian range boundaries, or when Samoa skips a
whole day, if your application use historic dates, and so on.
There are large libraries out there that will do all of this checking,
but you have to decide how much you care about the veracity of date
inputs and check accordingly. SQLite is not the tool/library to do this
with.
For some comic relief re computer date and time calculation problems, see:
https://www.youtube.com/watch?v=-5wpm-gesOY
