为什么在Date列中附加NULL会导致显示值1900

为什么在Date列中附加NULL会导致显示值1900-01-01？(Why does appending a NULL into a Date column result in the value 1900-01-01 being displayed?)

我正在使用SSIS包导入基本文本文件，它有3个日期字段，有时一些日期字段为空。

导入的表显示空字段，我想因为它们是varchar（50）数据类型。 但是我需要将那些表中的记录插入到另一个表中，其中这些列被定义为Date数据类型。

当我运行insert语句时，目标表中的结果值都显示日期为1900-01-01 ，而不是NULL或空白。 我试图强制该值为null，但它不起作用：

CASE WHEN refresh_date IS NULL THEN NULL ELSE refresh_date END AS RefreshDate

如何使Date列只接受空值或空值？

I'm using an SSIS package to import a basic text file, it has 3 date fields, and sometimes some of the date fields are empty.

The imported table shows empty fields, I suppose because they are varchar(50) datatypes. But then I need to insert those records from that table into another table, where those columns are defined as Date datatypes.

When I run the insert statement, the resulting values in the destination table all show 1900-01-01 for the date, rather than NULL or blank. I tried forcing the value to be null, but it didn't work:

CASE WHEN refresh_date IS NULL THEN NULL ELSE refresh_date END AS RefreshDate

How can I make a Date column just accept a blank or null value?

最满意答案

如果为'empty'，则不应将varchar字段转换为或转换为日期。 当空白或空字符串转换为日期时，它等于'1900-01-01'。 您可以使用以下算法对此进行测试：

SELECT CAST('' as date)

使用SSIS，您最好检查varchar（50）字段是否等于''，如果是，则将其设置为NULL。 这是一个示例SQL查询：

SELECT CASE WHEN importedfield = '' THEN NULL ELSE CAST(importedfield as date) END AS [NewFieldname]

The varchar field should not be casted or converted to a date if 'empty'. When a blank, or empty string, is casted to a date it equals '1900-01-01'. You can test this by using the following algorithm:

SELECT CAST('' as date)

Using SSIS you are better of checking if the varchar(50) field equals '', and if so setting it to NULL. Here is an example SQL query:

SELECT CASE WHEN importedfield = '' THEN NULL ELSE CAST(importedfield as date) END AS [NewFieldname]