我正在使用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 RefreshDateHow 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]更多推荐
发布评论