Convert Excel Serial Date to SQL datetime
David Berman
SQL database. The problem is that Excel sometimes stores dates in a
format called "serial date".
(http://www.cpearson.com/excel/datetime.htm). For example, 36,544 =
29-Jan-2000 because that's the number of days that have passed since
then. Genius huh? Anyway, I need to write a user defined function to
translate the excel value into a proper datetime format for sql server.
I don't want to edit the excel file because I have a program that is
processing excel files in batch to insert some values into the database
by calling a stored procedure. I'd rather not have to add another step
in the pipeline to first have to test the excel format for this format
of storage for dates and then convert it before importing the data with
this other proprietary software.
I found a conversion function written in C++
(http://www.codeproject.com/datetime/exceldmy.asp?df=100&forumid=4548&ex
p=0&select=258452) but I couldn't get it to work in C# or SQL (I
attempted to translate).
Thank you for any help or useful links!
David
Meet people for friendship, contacts,
or romance using free instant messaging software! See a picture you
like? Click once for a private conversation with that person!
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Steve Kass
As long as none of your dates is before March 1, 1900, it's very easy:
declare @ExcelDate int
set @ExcelDate = 38081 -- April 4, 2004
select cast(@ExcelDate-2 AS datetime)
declare @ExcelDateTime float
set @ExcelDateTime = 38081.5 -- noon on April 4, 2004
select cast(@ExcelDateTime-2 AS datetime)
Before March 1, 1900, you have to correct by 1, because in Excel the
invalid date February 29, 1900 exists.
Steve Kass
Drew University