CODEWITHSUNDEEP
sql-server-2008import-from-excel
CodingInCircles
CodingInCircles

Reputation: 2807

How to make the default date format dd/mm/yyyy in SQL Server 2008?

I have an Excel file that contains a column full of dates in the dd/mm/yyyy format. When I try to import it using openrowset, it said that there was a datatype mismatch. I have a table where the date is defined as type date. Now, I know that the default date format in SQL Server is yyyy-mm-dd. How can I avoid this conflict? Is there a way I can make the default date type be dd/mm/yyyy? I need to do this import operation everyday and it has to be automated and so I cannot afford it to fail in between. I tried using sp_addlanguage to make it British as the default date type is dd/mm/yyyy there, but it didn't work :(. I'm using SQL Server 2008 and Windows 7, if that is of any help. Please help me out! Thanks!

Upvotes: 0

Views: 5007

Answers (1)

Tom Morgan
Tom Morgan

Reputation: 2365

You could CONVERT the incoming data before you insert it. So, in the openrowset statement, where you select the field, you could surround it with a CONVERT statement. Here's an example:

print convert(date,'19/07/2010',103)

This is a UK style date, but if you run it you can see that it's converted it to SQL-friendly format.

Upvotes: 1

Related Questions