2016-05-05 10:08 GMT+02:00 R Smith <rsmith at rsweb.co.za>:
>
>
> 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.
>
?OK, it cannot be perfect, but I think it could be a lot better with little
work. For example:
2004-12-31 23:59:60
is a valid date-time, but gives back NULL. Seeing how liberal days are
handled I think that:
YYYY-12-31 23:59:60
always should be accepted. Except when YYYY itself is not acceptable. At
the moment valid times can be marked as invalid and invalid times as valid.
Probably imposable to completely circumvent, but it can be done a lot
better.
For February I think 30 and 31 is never an acceptable value, as is 31 for
April, June, ? As Stephan Beal commented the days in a year can be used to
verify if 29 February is allowed.
These changes would not be much work I think, but would improve the quality
of strftime a lot.
--
Cecil Westerhof
