SQL 如何使用 SSIS 派生列转换为 NULL 日期分配默认值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13166968/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to assign a default value to NULL dates using SSIS derived column transformation?
提问by
In my SQL 2008 SSIS environment, I have data coming from a CSV file and I need to fix up null dates.
在我的 SQL 2008 SSIS 环境中，我有来自 CSV 文件的数据，我需要修正空日期。
Data from CSV looks like this:
来自 CSV 的数据如下所示：
01011990
01011990
01011990
01011990
01011990
In my staging table, I have a column defined as:
在我的临时表中，我有一列定义为：
[SHIPPED DATE] date NOT NULL
First I fix the date values by pushing the column through a derived transformation that has:
首先，我通过将列推送到具有以下功能的派生转换来修复日期值：
(DT_DBDATE)(SUBSTRING([SHIPPED DATE],1,2) + "/" + SUBSTRING([SHIPPED DATE],3,2) + "/" + SUBSTRING([SHIPPED DATE],5,4))
(DT_DBDATE)(SUBSTRING([发货日期],1,2) + "/" + SUBSTRING([发货日期],3,2) + "/" + SUBSTRING([发货日期],5,4))
The above transformations makes my string dates coming form CSV import into a date column in my SQL db.
上述转换使我的字符串日期从 CSV 导入到我的 SQL 数据库中的日期列中。
The output of this is:
这个的输出是：
1990-01-01
1990-01-01
1990-01-01
1990-01-01
1990-01-01
Next, I'd like to deal with NULL dates so that I can stay true to the "NOT NULL" definition in my db. What I want to do is assign the NULL dates a default value but I get an error message as per below:
Next，我想处理 NULL 日期，以便我可以忠于我的数据库中的“NOT NULL”定义。我想要做的是为 NULL 日期分配一个默认值，但我收到一条错误消息，如下所示：
Error at CSV to SQL [Derived Column [24817]]: The data types "DT_WSTR" and "DT_DBDATE" are incompatible for the conditional operator. The operand types cannot be implicitly cast into compatible types for the conditional operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.
CSV 到 SQL [派生列 [24817]] 处的错误：数据类型“DT_WSTR”和“DT_DBDATE”与条件运算符不兼容。操作数类型不能隐式转换为条件操作的兼容类型。要执行此操作，需要使用转换运算符显式转换一个或两个操作数。
My CSV column is WSTR, my destination in the SQL DB is a "date" column.
我的 CSV 列是 WSTR，我在 SQL DB 中的目标是“日期”列。
My transformation I am trying to use is: ISNULL([SHIPPED_DATE]) ? "1900-01-01" : [SHIPPED_DATE]
我尝试使用的转换是： ISNULL([SHIPPED_DATE]) ？“1900-01-01”：[SHIPPED_DATE]
It does not want to work due to the above message. Can anyone point me to the right direction?
由于上述消息，它不想工作。谁能指出我正确的方向？
回答by
Change your expression as shown below. You have two parts to the ternary operator.
更改您的表达式，如下所示。三元运算符有两个部分。
Part 2you have it as [SHIPPED_DATE], which is of type DT_DBDATEbecause you are already casting this value in the derived column transformation.
Part 2你有它 as [SHIPPED_DATE]，这是DT_DBDATE因为你已经在派生列转换中转换了这个值。
However, Part 1is simply assigned as text "1900-01-01". You need to add (DT_DBDATE) before that string value to type cast it to date format so that both the values specified on the ternary operator are of same data type.
然而，Part 1被简单地分配为 text "1900-01-01"。您需要DT_DBDATE在该字符串值之前添加 ( ) 以将其类型转换为日期格式，以便三元运算符上指定的两个值具有相同的数据类型。
From
从
ISNULL([SHIPPED_DATE]) ? "1900-01-01" : [SHIPPED_DATE]
To
到
ISNULL([SHIPPED_DATE]) ? (DT_DBDATE)"1900-01-01" : [SHIPPED_DATE]
