On 2017/09/25 2:23 PM, Stephan Buchert wrote:
I was just going to write that you can easily convert an MS serial date
value stored in Sqlite to a date string (using 40777 as example):
sqlite> select date('1899-12-31', 40777||' days');
2011-08-23
However, according to
https://support.office.com/en-us/article/DATEVALUE-function-df8b07d4-7761-4a93-bc33-b7471bbff252
the answer should be 2011-08-22.
1900 was not a leap year, 2000 was, can it be, that the MS Excel software
has this wrong?
That is not the only thing they have wrong. The standard is good though,
you will find that other software using this scheme actually starts with
day 0 as 30 December 1899, so that day 1 is 31 December 1899 and day 2
only turns out to be 1 Jan 1900. In this way it continues to February 28
1900 being day 60 (whereas in Excel day 60 is 29 Feb 1900) which means
from Day 61 the two date-systems are in alignment, so the misalignment
only exists for the first 2 months of the date range.... Still, that is
a really weird way of doing things for Excel.
Which means... if you really want your SQlite dates to tie up to or
output Excel-happy time values, you have to do SELECT date('1899-12-30',
daycount||' days') which will pop out a date that is the same as other
software using this scheme and also same as Excel's date so long as
daycount > 60.
Another crazy thing Excel does - Day 0 is NOT given as 31-December-1899,
but indeed as 00-Jan-1901. Yes, that's a month with a ZERO day in it,
which means that day -1 would be 31-Dec-1899 while in every other system
it is 29 Dec 1899... How did Excel fix this? Simple, any date with
negative value gets a #VALUE! - i.e: "Sorry boss, can't compute!".
I can't be sure if Excel was the root cause of this bug, perhaps they
tried to conform to an earlier mistake, or perhaps long ago software in
general wasn't aware (or not in agreement) over whether 1900 was a leap
year or not - but in my travels I only found Excel showing 1900 as a
leap year along with these other funnies, so I'm leaning towards blaming
them.
Here is a quick copy-paste from Excel with one column (A) showing
integer numbers and the second (B) the same but formatted as dates using
the formula: =TEXT(A1,"YYYY-MM-DD")
(A1 being the column to the left, increasing in row number for every
subsequent row.). Dates used by everyone else given in C. As you can
see, things normalize after row 60, but before then Excel is just weird.
A B C
-2 #VALUE! 1899/12/28
-1 #VALUE! 1899/12/29
0 1900-01-00 1899/12/30
1 1900-01-01 1899/12/31
2 1900-01-02 1900/01/01
3 1900-01-03 1900/01/02
4 1900-01-04 1900/01/03
5 1900-01-05 1900/01/04
6 1900-01-06 1900/01/05
7 1900-01-07 1900/01/06
8 1900-01-08 1900/01/07
9 1900-01-09 1900/01/08
10 1900-01-10 1900/01/09
11 1900-01-11 1900/01/10
12 1900-01-12 1900/01/11
13 1900-01-13 1900/01/12
14 1900-01-14 1900/01/13
15 1900-01-15 1900/01/14
16 1900-01-16 1900/01/15
17 1900-01-17 1900/01/16
18 1900-01-18 1900/01/17
19 1900-01-19 1900/01/18
20 1900-01-20 1900/01/19
21 1900-01-21 1900/01/20
22 1900-01-22 1900/01/21
23 1900-01-23 1900/01/22
24 1900-01-24 1900/01/23
25 1900-01-25 1900/01/24
26 1900-01-26 1900/01/25
27 1900-01-27 1900/01/26
28 1900-01-28 1900/01/27
29 1900-01-29 1900/01/28
30 1900-01-30 1900/01/29
31 1900-01-31 1900/01/30
32 1900-02-01 1900/01/31
33 1900-02-02 1900/02/01
34 1900-02-03 1900/02/02
35 1900-02-04 1900/02/03
36 1900-02-05 1900/02/04
37 1900-02-06 1900/02/05
38 1900-02-07 1900/02/06
39 1900-02-08 1900/02/07
40 1900-02-09 1900/02/08
41 1900-02-10 1900/02/09
42 1900-02-11 1900/02/10
43 1900-02-12 1900/02/11
44 1900-02-13 1900/02/12
45 1900-02-14 1900/02/13
46 1900-02-15 1900/02/14
47 1900-02-16 1900/02/15
48 1900-02-17 1900/02/16
49 1900-02-18 1900/02/17
50 1900-02-19 1900/02/18
51 1900-02-20 1900/02/19
52 1900-02-21 1900/02/20
53 1900-02-22 1900/02/21
54 1900-02-23 1900/02/22
55 1900-02-24 1900/02/23
56 1900-02-25 1900/02/24
57 1900-02-26 1900/02/25
58 1900-02-27 1900/02/26
59 1900-02-28 1900/02/27
60 1900-02-29 1900/02/28
61 1900-03-01 1900/03/01
62 1900-03-02 1900/03/02
63 1900-03-03 1900/03/03
64 1900-03-04 1900/03/04
65 1900-03-05 1900/03/05
66 1900-03-06 1900/03/06
67 1900-03-07 1900/03/07
68 1900-03-08 1900/03/08
69 1900-03-09 1900/03/09
70 1900-03-10 1900/03/10
PS: I refer to "Excel" only, but the problem probably persists in all of
MS Office, though I didn't check.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
