从平面文件导入SQL数据库时的日期格式问题 [英] Date format issue while importing from a flat file to an SQL database

问题描述

我创建了一个SSIS包，其中数据是从扁平的flie中获取的，并被传输到数据库。

I Created an SSIS package where the Data is taken in from a flat flie and is transferred to the database.

但是这有一个令人困惑和有趣的解释效果日期不一致。
任何日期，当天大于本月十二日将正确的
解释为dd / mm / yyyy，但任何小于此值将向后。

But this had the confusing and hilarious effect of interpreting dates inconsistently. Any dates, where the day was greater than the 12th of the month would be correctly interpreted as dd/mm/yyyy but anything less than that would be backwards.

所以02/15/2012将被正确阅读为2012年2月15日。

So 02/15/2012 would be correctly read as the 15th of Feb 2012.

但是02 / 09/2012将很高兴被解释为2012年9月2日。

But 02/09/2012 would happily be interpreted as 2nd of Sep 2012.

推荐答案

这个问题很常见，我遇到了很多次。

This issue is common and I have encountered it many times.

首先，检查您的SSIS区域设置是否未设置为美国。

您需要将其设置为英文（United Kindom）或荷兰（荷兰）。

要执行此操作，请单击SSIS包中的任意位置并查看属性并设置LocaleID（在Misc下）。在连接管理器中，选择flat_source_file连接并编辑属性面板中的LocaleID。
如果没有帮助，那么请执行以下操作：

First thing, check if your SSIS Locale is not set to United States.
You need to set it to English(United Kindom) or Dutch (Netherlands).
To do that click anywhere in your SSIS package and view properties and set LocaleID (under Misc). In Connection Managers, select the flat_source_file Connection and edit the LocaleID in the properties panel. If that doesn't help then do the following:

我将平面文件源中的源列设置为字符串数据类型。

然后使用数据转换转换将日期列转换为 DT_DATE 或 DT_DBDate 。

I would set the source column in your 'Flat File Source' to string datatype.
Then use a 'Data Conversion' transformation to cast the date column as either DT_DATE or DT_DBDate.

DT_DATE 是一个日期结构包括年，月，日和小时。

DT_DBDATE 是一个包含年，月和日的日期结构。 />

DT_DATE is a date structure that consists of year, month, day, and hour.
DT_DBDATE is a date structure that consists of year, month, and day.

现在在目标窗口中使用新的转换列。

Now use the new transformed column in the destination window.

这篇关于从平面文件导入SQL数据库时的日期格式问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！