CODEWITHSUNDEEP
sql-serverexceldatems-office
bsivel
bsivel

Reputation: 2959

Excel Text or General Formatted Date Import and Conversion in SQL Server

My colleagues have an excel worksheet with dates, currency and percentages that get formatted to "General" before they export the sheet as a tab separated text file. This is done to capture all raw data and eliminate formatting and rounding. For example 30% returns to it original value of 0.29854. The export file is imported into SQL Server.

My problem is the dates get set to an exell number and I have difficulty in converting it to a date once its imported into SQL Server. Examples are below:

Date Fmt    General Fmt
9/3/2008    39694
7/1/2010    40360
5/4/2011    40667

Does anyone know how I can import the General Format value into SQL Server as a varchar and then convert it back to a date?

The obvious answer is to NOT format the dates as General but I have no control over this process and cannot change it.

I have tried to use DATEADD() to reconvert the date from 1/1/1900. This is date 1 as far a excel is concerned. Reference the following for more on this: http://www.exceltactics.com/definitive-guide-using-dates-times-excel/#How-Excel-Stores-Dates

The problem I find with this solution is its two days off.

select dateadd(d,39694,'1/1/1900') = 9/5/2008  { should be 9/3/2008 }
select dateadd(dd,40360,'1/1/1900') = 7/3/2010 { should be 7/1/2010 }
select dateadd(dd,40677,'1/1/1900') = 5/6/2011 { should be 5/4/2011 }

I recognize this is most likely a rounding error but is there a better way to convert this to capture the correct date in SQL Server?

If I simply adjust to DateAdd(d,#####,'12/30/1899') I do get the right answer but somewhere in the future this too will be a day off. I'm not smart enough to figure out when this may happen...

Thanks for any help.

Upvotes: 1

Views: 884

Answers (1)

Karel-Jan Misseghers
Karel-Jan Misseghers

Reputation: 807

Excel date serial values are a bit screwed up. Microsoft would blame Lotus 1-2-3, no doubt.

Given an Excel serial value that represents a date from 19000301 onwards, you can subtract 2 to get an integer that SQL Server will convert to datetime for the same date.

Given an Excel serial value that represents a date between 19000101 and 19000228, you can subtract 1 to get an integer that SQL Server will convert to datetime for the same date.

Given an Excel serial value of 60, you should panic, since this represent the fictitious date of 19000229.

Source: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=27101 (Copy from user 'X002548' on that forum)

Upvotes: 1

Related Questions