Excel date serial number convertion in SQL2000
-
Hi,
In excel, any datetime is stored as serial number. e.g. 12/13/2006 is stored as 39064. To convert this to datetime in SQL, I am trying to use the following code:
select convert(varchar(8), convert(datetime,39064),112)
and it produces this: 20061215.
My question is, Am I right in using the code above to achieve what I want? There is a date difference of 2 days from excel to SQL. Has anybody faced anything like this and can anybody suggest a way to solve it.
-
Saibhushan - How do you get 39064 ?! I see it being stored as 39066 and running your select displays "20061217" ?!?!
**ASCII stupid question, get a stupid ANSI !!!** -
The date serial between Excel, VB, and SQL Server vary a bit (a day or two in some cases). And, Excel thinks that 1900 was a leap year... don't use date serials for data transfers.
--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) -
Saibhushan...sorry - don't know what I was looking at...it is 39064...to make up I am providing links for 2 articles in the hope that reading them may provide better guidance...
moral of the post...don't post when you're in a hurry and 5 minutes away from packing for the holidays...
Talking of which - happy holidays to you Jeff...couldn't respond to the other post..always nice "seeing" you too!
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply