使用SSIS for Excel到SQL Server的日期格式问题 [英] Date format problem using SSIS for Excel into SQL Server

问题描述

我正在尝试使用SSIS将Excel 2003中的电子表格中的一列日期导入SQL Server 2005。我在英国，所以想要日期格式为dd / MM / yyyy。

I am trying to import a column of dates from a spreadsheet in Excel 2003 into SQL Server 2005 using SSIS. I am in the UK so want dates formatted as dd/MM/yyyy.

不幸的是，电子表格中的列包含以dd / MM / yyyy（使用Excel常规格式）以字符串形式存储的日期以及使用Excel的日期的混合日期格式为dd / MM / yyyy（语言环境为英语（英国））。

Unfortunately, the column in the spreadsheet contains a mixture of dates stored as strings in dd/MM/yyyy (with Excel 'General' formatting) as well as dates using Excel 'Date' formatting dd/MM/yyyy (with locale 'English (United Kingdom)').

这就是它的样子，我不能指望用户能够自己解决这个问题。

This is just the way it is and I can't expect the users to be able to sort this out themselves.

在查看电子表格时，所有日期在视觉上都显示正确，即dd / MM / yyyy。

When looking at the spreadsheet, all of the dates visually appear correct i.e. dd/MM/yyyy.

我正在尝试将值导入varchar列在数据库的保存表中。然后，我运行一个存储过程，将这些值复制到包含datetime列的适当表中。

I am trying to import the values into a varchar column in a holding table in the database. Then I run a stored procedure that copies these values into the proper table which contains a datetime column.

问题是存储在电子表格中并使用Date的日期格式化将作为MM / dd / yyyy导入SQL Server，并且存储为字符串的日期将作为dd / MM / yyyy导入。我的连接字符串中有IMEX = 1。

The problem is that the dates that are stored in the spreadsheet and use Date formatting get imported as MM/dd/yyyy into SQL Server and the dates stored as strings are getting imported as dd/MM/yyyy. I have IMEX=1 in the connection string.

在同一varchar列中同时使用两种格式的日期显然会在我尝试将其放入datetime列时引起问题，因此

Having dates using both formats in the same varchar column is obviously causing a problem when I try to put it into a datetime column, hence

SET DATEFORMAT MDY;    
SET DATEFORMAT DMY;

没用。

有有人知道为什么SSIS包会以MM / dd / yyyy的形式将Excel电子表格中看似正确的日期导入到SQL Server中吗？

Does anyone know why the SSIS package would import the seemingly correct dates in the Excel spreadsheet into SQL Server as MM/dd/yyyy anyway?

反正有强制SSIS包的地方吗？将日期导入为dd / MM / yyyy，该日期将与这种日期组合作为字符串和单元格并应用日期格式一起使用？

Is there anyway to force the SSIS package to import the dates as dd/MM/yyyy that will work with this combination of dates as strings and cells with date formatting applied?

还有其他想法吗？！

非常感谢，

Anthony

推荐答案

我认为您已经回答了自己的问题。日期格式的单元格的导入被视为日期，其他单元格被视为字符串。您的SQL Server日期设置可能是MM / dd / yyyy，因此是转换。

I think you have answered your own question. The import of date formatted cells are treated as dates and others as a string. Possibly you SQL server date setting is MM/dd/yyyy and hence the conversion.

为什么不尝试在SSIS包中添加数据转换步骤并转换一切在列中以单一格式-日期时间或字符串。然后，我确定SQL Server将以相同的方式处理所有这些。

Why don't you try adding a data conversion step in you SSIS package and convert everyting in the column into a single format - datetime or string. Then I am sure SQL server will handle all of them the same way.

Raj

这篇关于使用SSIS for Excel到SQL Server的日期格式问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！