excel error

Question

excel error

Anamika

SSCertifiable

Points: 6202
More actions
December 10, 2009 at 4:55 am

#139974

Hi,
I'm getting a peculiar problem.
for example i entered 2/2/2010 in excel and integrating it to notepad. itz been interpreted as 40211. if i try to get the date for that number in sql server using
select datepart(dd,40211),datepart(mm,40211),datepart(yy,40211)
i'm getting
(No column name) (No column name) (No column name)
4 2 2007
which is not correct.
how to get the correct dates?
Thanks,
Regards,
Anamika

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246697 More actions · Answer 1

select cast(40211 as datetime)

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Anamika SSCertifiable Points: 6202 More actions · Answer 2

Hi,

while I use cast also i get the same answer.

I repeat my question :

for the date '2/2/2010' i got 40211.

when you use the date functions to get the date I'm getting 4th february instead of 2nd february. is there any reason behind it?

how can i solve this.

thanks,

regards

anamika

Phil Parkin SSC Guru Points: 246697 More actions · Answer 3

Excel and SQL Server have different dates for 'Day 0'.

In Excel, it is 1900-01-00 and in SQL Server it is 1900-01-01. That accounts for one day's difference.

The other day I am not certain about, but I have read about a slight error in Excel's implementation, in that the year 1900 was interpreted as a leap year, so Excel's days calculation assumes that there was a 29th February in the year 1900 (which there was not). Try typing this date into Excel and you will see that it validates as a date.

So - that explains both days. You just need to subtract 2 from the SQL Server cast date

select cast((ExcelDays - 2) as datetime)

This. of course, stops working if any of year dates are earlier than 1900-03-01, for the reason explained above.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.