?
Solved

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

Posted on 2008-10-26
2
Medium Priority
?
799 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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

612 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