Coverting float to datetime

For some reason if I do a convert to a datetime, the time ends up being 2
days off. For example if I do a cast or convert of 38027 I get 2004-02-12.
If I put that same value in excel and make the field date, it shows
2004-02-10. The 10th is the correct date and its making me crazy trying to
figure out what the problem is. I have tried using decimal as opposed to
float and I still get the same results. Any ideas out there?James,
Two days difference come because of two reasons:
1. SQL Server assumes 1.1.1900 as 0 - check SELECT CAST(0 AS datetime),
while Excel as 1
2. Excel incorrectly assumes 1900 is a leap year, as described at
http://support.microsoft.com/defaul...NoWebContent=1.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
news:#hqEgd79DHA.4080@.tk2msftngp13.phx.gbl...
> For some reason if I do a convert to a datetime, the time ends up being 2
> days off. For example if I do a cast or convert of 38027 I get
2004-02-12.
> If I put that same value in excel and make the field date, it shows
> 2004-02-10. The 10th is the correct date and its making me crazy trying
to
> figure out what the problem is. I have tried using decimal as opposed to
> float and I still get the same results. Any ideas out there?
>|||But I am using Excel XP and Excel is the one that is reporting the date
correctly. If I do select convert(float,getdate()) and paste that number in
excel, convert the field to date, it show it as two days ago. Something is
amiss.
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:uRRTbs79DHA.2524@.TK2MSFTNGP11.phx.gbl...
> James,
> Two days difference come because of two reasons:
> 1. SQL Server assumes 1.1.1900 as 0 - check SELECT CAST(0 AS datetime),
> while Excel as 1
> 2. Excel incorrectly assumes 1900 is a leap year, as described at
>
http://support.microsoft.com/defaul...NoWebContent=1.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
> news:#hqEgd79DHA.4080@.tk2msftngp13.phx.gbl...
2
> 2004-02-12.
> to
to
>|||Why do you expect that converting to some strange float representation will
result in a meaningful value? Especially assuming that two products will use
the same rules when converting from some strange float value to datetime?
Excel and SQL Server uses different internal representation of datetime.
This is why you never should rely on the internal representation of the
datetime datatype. Always use the supported character representation. (IMO,
SQL Server shouldn't even allow for such CAST or CONVERT...)
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
news:uMJxS579DHA.3488@.tk2msftngp13.phx.gbl...
> But I am using Excel XP and Excel is the one that is reporting the date
> correctly. If I do select convert(float,getdate()) and paste that number
in
> excel, convert the field to date, it show it as two days ago. Something
is
> amiss.
>
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
> message news:uRRTbs79DHA.2524@.TK2MSFTNGP11.phx.gbl...
>
http://support.microsoft.com/defaul...NoWebContent=1.
being
> 2
trying
> to
>|||Thanks for nothing. That was real helpful.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uTlYuC89DHA.2368@.TK2MSFTNGP11.phx.gbl...
> Why do you expect that converting to some strange float representation
will
> result in a meaningful value? Especially assuming that two products will
use
> the same rules when converting from some strange float value to datetime?
> Excel and SQL Server uses different internal representation of datetime.
> This is why you never should rely on the internal representation of the
> datetime datatype. Always use the supported character representation.
(IMO,
> SQL Server shouldn't even allow for such CAST or CONVERT...)
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=...ublic.sqlserver
>
> "James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
> news:uMJxS579DHA.3488@.tk2msftngp13.phx.gbl...
number
> in
> is
in
datetime),
>
http://support.microsoft.com/defaul...NoWebContent=1.
> being
> trying
opposed
>|||Agree with Tibor, just to add - have you checked the link in the bullet 2.
of my previous answer? MS says there is bug in Execl. Chek on some calendar,
1900 really was not a leap year.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uTlYuC89DHA.2368@.TK2MSFTNGP11.phx.gbl...
> Why do you expect that converting to some strange float representation
will
> result in a meaningful value? Especially assuming that two products will
use
> the same rules when converting from some strange float value to datetime?
> Excel and SQL Server uses different internal representation of datetime.
> This is why you never should rely on the internal representation of the
> datetime datatype. Always use the supported character representation.
(IMO,
> SQL Server shouldn't even allow for such CAST or CONVERT...)
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=...ublic.sqlserver
>
> "James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
> news:uMJxS579DHA.3488@.tk2msftngp13.phx.gbl...
number
> in
> is
in
datetime),
>
http://support.microsoft.com/defaul...NoWebContent=1.
> being
> trying
opposed
>|||I was trying to pass the message that using anything other than a character
representation will eventually lead to these type of situations. AFAIK, SQL
Server doesn't even document what conversion from datetime to float will be.
In other words, they could change that in the next version.
My apologies if the post came out as unhelpful or rude. That was not my
intent.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
news:%23DVtmd89DHA.1268@.TK2MSFTNGP12.phx.gbl...
> Thanks for nothing. That was real helpful.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:uTlYuC89DHA.2368@.TK2MSFTNGP11.phx.gbl...
> will
> use
datetime?
> (IMO,
>
http://groups.google.com/groups?oi=...ublic.sqlserver
date
> number
Something
wrote
> in
> datetime),
>
http://support.microsoft.com/defaul...NoWebContent=1.
shows
> opposed
>