Serial date returns different dates in SLServer vs Excel?

Question

Serial date returns different dates in SLServer vs Excel?

Paul McCurdy

Ten Centuries

Points: 1377
More actions
June 12, 2008 at 1:37 pm

#70854

I'm importing an Excel spreadsheet into a SQLServer table. One of the columns I use is a serial date in the Excel source file e.g. 39623 which, when I format it in Excel, returns a date of 6/24/2008. The destination table in SQLServer is formatted as an int. When I CAST this to a DATETIME I end up with a different date i.e. 6/26/2008. Somehow I gained 2 days in the translation. Anyone else experienced this? I can work around it of course, but seems kind of strange that different MS products would handle differently. :crazy:

Viewing 8 posts - 1 through 7 (of 7 total)

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

hehe - It looks to me that the programmer in charge of date handling for excel was asleep at the wheel. Two things are causing your issue:

1. 0 doesn't mean the same thing to both systems. In Excel - formatting a 0 to be a date results in an invalid date ("01/00/1900", or in their definition - the day before 1/1/1900. Fancy that - I thought that was 12/31/1899...)

2. Excel incorrectly treats 1900 as a leap year (rule is: a year is a leap year if its divisible by 4 UNLESS it's divisible by 100 and NOT divisible by 400). So - february 1900 gets an extra day it didn't have.....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 2

The sad truth is that the Excel bugs are intentional "features" that were put in to match the behavior of Lotus 123 where the bug originated.

http://support.microsoft.com/kb/214326

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Michael Valentine Jones (6/12/2008)
The sad truth is that the Excel bugs are intentional "features" that were put in to match the behavior of Lotus 123 where the bug originated.
http://support.microsoft.com/kb/214326

It's actually a better reason than just plain screwing up. Thanks for the link back.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

I'd like to know why people use Date Serials in such a fashion to begin with. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 5

Date "Zero" for SQL Server is 1900-01-01

Date "Zero" for MS Access is 1899-12-30

That's why there is a two day difference.

N 56°04'39.16"
E 12°55'05.25"

Paul McCurdy Ten Centuries Points: 1377 More actions · Answer 6

I hear you. Hey, the use of Excel wasn't my choice, but unfortunately I had to deal with it 🙁

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 7

So if you want to keep dateserials, just add 2 to excels dateserial value and you're set.

Or convert excel dateserial back to date, export and convert back to dateserial.

N 56°04'39.16"
E 12°55'05.25"