SQL convert(datetime, number) wrong?
-
I tried this
select convert(datetime, 41818) as mydate
result
2014-06-30 00:00:00.000
In Excel, it should be
6/28/2014
It's always 2 days in advance Please help! Thanks a lot
Why do you think it's wrong? SELECT DATEDIFF ( dd , '1900-01-01' , '2014-06-30' ) SELECT DATEADD ( dd, 41818 , '1900-01-01' )
Miller Nguyen wrote:
In Excel, it should be
What do you mean "it should be" ?
You'll never get very far if all you do is follow instructions.
-
I tried this
select convert(datetime, 41818) as mydate
result
2014-06-30 00:00:00.000
In Excel, it should be
6/28/2014
It's always 2 days in advance Please help! Thanks a lot
What the f...? You are right, I tested it. Look at day number 0: with SQL Server, it is Jan 1, 1900; with Excel: Jan
0, 1900. The next bug is the leap year: Excel treats 1900 as a leap year (that's wrong!), while SQL Server correctly knows that 1900 is not a leap year. In sum, those differences account for the 2 days difference in current dates. -
What the f...? You are right, I tested it. Look at day number 0: with SQL Server, it is Jan 1, 1900; with Excel: Jan
0, 1900. The next bug is the leap year: Excel treats 1900 as a leap year (that's wrong!), while SQL Server correctly knows that 1900 is not a leap year. In sum, those differences account for the 2 days difference in current dates.Wrong is evil and must be defeated. - Jeff Ello[^]
-
I tried this
select convert(datetime, 41818) as mydate
result
2014-06-30 00:00:00.000
In Excel, it should be
6/28/2014
It's always 2 days in advance Please help! Thanks a lot
See my answer to Bernhard
Wrong is evil and must be defeated. - Jeff Ello[^]
-
Why do you think it's wrong? SELECT DATEDIFF ( dd , '1900-01-01' , '2014-06-30' ) SELECT DATEADD ( dd, 41818 , '1900-01-01' )
Miller Nguyen wrote:
In Excel, it should be
What do you mean "it should be" ?
You'll never get very far if all you do is follow instructions.
I don't say it's wrong, that's why I put the QUESTION MARK in the topic. I just wonder why Excel and SQL are giving different result
-
See my answer to Bernhard
Wrong is evil and must be defeated. - Jeff Ello[^]
Thanks a lot
-
Thanks a lot
-
For example, -0.5 and 0.5 both mean noon on 30 December 1899
No, no, no! I do not want to use that function!
-
I tried this
select convert(datetime, 41818) as mydate
result
2014-06-30 00:00:00.000
In Excel, it should be
6/28/2014
It's always 2 days in advance Please help! Thanks a lot
I get around this, and other issues such as collation issues between SQL Server and Excel, by always passing dates to Excel as text in a format of dd-MMM-yyyy e.g. '01-Jan-2014'. It's not pretty but it has worked so far...
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
For example, -0.5 and 0.5 both mean noon on 30 December 1899
No, no, no! I do not want to use that function!
:laugh: It's not a bug, it's a feature.
Wrong is evil and must be defeated. - Jeff Ello[^]
-
Wrong is evil and must be defeated. - Jeff Ello[^]
Yeah, as I drifted off to sleep last night I recalled that Joel had written about Excel having to support a Lotus 123 issue. :thumbsup:
You'll never get very far if all you do is follow instructions.
-
Yeah, as I drifted off to sleep last night I recalled that Joel had written about Excel having to support a Lotus 123 issue. :thumbsup:
You'll never get very far if all you do is follow instructions.
I love that story. They brought a fuck counter to all meetings with BillG. :laugh:
Wrong is evil and must be defeated. - Jeff Ello[^]