Re: SQL UDF problem

On Mon, Dec 9, 2013 at 7:44 AM, CRPence <CRPbottle@xxxxxxxxx> wrote:

And then I am left wondering how the "+2" fits in with the "# of days
since 01/01/1900". Seems something is amiss, given the input value of
19000101 would return two versus zero.? I suppose if the intention was
to represent the number of days since 31-Dec-1899 inclusive of the date
offered as input, then adding two to the difference makes sense. But
what about numeric values representing dates from before that apparent
start date; almost seems the test for greater-than zero maybe should be
greater than 19000100.?

The early design of dates in Excel borrows heavily from Lotus 1-2-3,
and Lotus has a flaw, due either to ignorance, carelessness, or
deliberate risk calculation that users will not need dates before
1900-03-01.

Yes, I said March 1, 1900.

You see, the original date calculation considers 1900-02-29 a valid
date. I.e., it incorrectly considers 1900 a leap year.

It numbers the days starting with 1900-01-01 as 1, 1900-01-02 as 2,
etc. until 1900-02-28 as 59, then 1900-02-29 as 60, 1900-03-01 as 61,
and then consistently adds one from there.

Peter's solution matches Excel for 1900-03-01 and later.

John