Solved

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

Posted on 2008-10-26
2
753 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

863 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now