Solved

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

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
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…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
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.

726 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