Solved

How do I convert Excel Date to SQL Date during import?

Posted on 2008-10-26
2
764 Views
Last Modified: 2013-12-17
Hello,

I have an excel file containing about 30 columns and a few hundred rows.  The entire file needs to be imported to an existing MSSQL table.  However, one column in the excel file is using date format of MM/DD/YYYY which causes an error at import.  How can I convert this date to SQL format (YYYY-MM-DD) at time of import?  This format is not one of the support default date styles in Excel.

Thank you for the help.
0
Comment
Question by:trumpman
2 Comments
 
LVL 20

Accepted Solution

by:
Marten Rune earned 500 total points
ID: 22808150
first is this a one time job???

If so, why not all an extra column as varchar and let it import as text i e 'MM/DD/YYYY'

Then run an update clause like in my example below
This should do the trick
/Marten
create table test(
ID int identity,
realdate datetime null,
strdatum varchar(20) not null
)
GO
insert into test (strdatum)
values ('07/05/1966')
GO
insert into test (strdatum)
values ('12/24/2008')
GO
 
update test
Set realdate = cast(right(strdatum,4)+ left(strdatum,2)+left(right(strdatum,7),2) as datetime)
GO
 
select *
from test
GO
 
Drop table test
GO

Open in new window

0
 

Author Comment

by:trumpman
ID: 22808242
@martenrune

Yes it is.  

Thank you for the assistance.  Your solution does the trick.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question