bradtm
asked on
Excel Serial Date converting to SQL Server
I have an Excel worksheet that has information from another system. one particular cell has date data, but in two different formats.
The first appears as "1-Jun-95" which is in a datetime format. The second appears as "March 20, 1996 June 24, 1994" which is in a text format. I have a function in SQL server that will parse out these two dates and put them to good use. The problem begins when I import this data into SQL Server. If the cells in Excel are formated into datetime then the values with two dates will become null, which is unacceptable. If I convert the cells to text then the date cells all become Excel Serial Dates like "34851". I tried to use the concatenate function on the cells to add a single quote to each cell, but it uses the Serial Date instead of the displayed datetime. I don't want to use this Excel Serial value at all.
Is there a conversion function in SQL that will convert "34851" to "1-Jun-95"?
or
Is there a way to properly capture the classic DB datetime format as the data is imported from Excel into SQL Server?
Either way, in the end I want my data to look like the following in SQL Server:
1-Jun-95
20-Mar-96
March 20, 1996 June 24, 1994
20-Mar-96
March 20, 1996 June 1, 1995
The table I've been dumping this info to is set as a varchar to compensate for the double dates.
The first appears as "1-Jun-95" which is in a datetime format. The second appears as "March 20, 1996 June 24, 1994" which is in a text format. I have a function in SQL server that will parse out these two dates and put them to good use. The problem begins when I import this data into SQL Server. If the cells in Excel are formated into datetime then the values with two dates will become null, which is unacceptable. If I convert the cells to text then the date cells all become Excel Serial Dates like "34851". I tried to use the concatenate function on the cells to add a single quote to each cell, but it uses the Serial Date instead of the displayed datetime. I don't want to use this Excel Serial value at all.
Is there a conversion function in SQL that will convert "34851" to "1-Jun-95"?
or
Is there a way to properly capture the classic DB datetime format as the data is imported from Excel into SQL Server?
Either way, in the end I want my data to look like the following in SQL Server:
1-Jun-95
20-Mar-96
March 20, 1996 June 24, 1994
20-Mar-96
March 20, 1996 June 1, 1995
The table I've been dumping this info to is set as a varchar to compensate for the double dates.
EugeneZ
can you change Excel's to text format?
>>Is there a conversion function in SQL that will convert "34851" to "1-Jun-95"?<<
Yes. Check out the date add function.
Yes. Check out the date add function.
Let me try that again:
Yes. Check out the T-SQL DateAdd function.
Yes. Check out the T-SQL DateAdd function.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Excel and MS SQL Server use the same base date, namely: 1 Jan 1900
Ignore my last comment, it looks I am wrong. Today is:
MS SQL Server = 38374
MS Excel = 38376
MS SQL Server = 38374
MS Excel = 38376
ASKER
Scott seems to be right. They seem to add up correctly and I'm able to extract a useable date from it. I'm wondring why it's Dec 30th of 1899 instead of acperkins Jan 1 1900?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I think Excel is also counting the actual number of separate days whereas SQL counts the number of day *boundaries* crossed (from 11:59pm to 00:00 of the next day) -- a subtle difference that reduces the SQL "count" / serial date value.