Solved

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

Posted on 2008-10-26
2
742 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
Comment Utility
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
Comment Utility
@martenrune

Yes it is.  

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

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

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…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

772 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

10 Experts available now in Live!

Get 1:1 Help Now