Data Conversion NULL to Date

Question

Data Conversion NULL to Date

hal9000-869914

SSC Enthusiast

Points: 169
More actions
August 21, 2009 at 10:11 am

#134386

Hi,
I have a staging table with a column called WADAT. There I have string
dates like '22.05.2009' and empty strings. I am using a derived column
expression to convert the empty strings to null values.
LEN(TRIM(WADAT)) == 0 ? (DT_STR,1,1252)NULL(DT_STR,1,1252) : TRIM
(WADAT)
No I want to convert these values to date values. In the convertion
task I get the error
... The value could not be converted because of a potential loss of
data ...
Please Help...
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Erik Kutzler SSCarpal Tunnel Points: 4287 More actions · Answer 1

I don't know what your regional date settings are, but I'm going to assume that the Data Conversion component doesn't like the periods (.) in your date string.

Changing your Derived Column expression to

LEN(RTRIM(WADAT)) == 0 ? (DT_STR,1,1252)NULL(DT_STR,1,1252) : SUBSTRING(WADAT,1,2) + "/" + SUBSTRING(WADAT,4,2) + "/" + SUBSTRING(WADAT,7,4)

or

LEN(RTRIM(WADAT)) == 0 ? (DT_STR,1,1252)NULL(DT_STR,1,1252) : REPLACE(WADAT,".","/")

will work.

hal9000-869914 SSC Enthusiast Points: 169 More actions · Answer 2

The problem is that I have another column where I have no empty values and it works. Strange thing...

Tim Mitchell SSCoach Points: 15681 More actions · Answer 3

You're using 2 different string lengths in your expression. Your NULL condition uses a 1-character string, but the WADAT column would be larger than that since '22.05.2009' is a possible value. You'll want to change the NULL part of your expression to reflect the length of the column named WADAT. If it is a 50-character column, for example, you'd replace this part of your expression:

(DT_STR,1,1252)NULL(DT_STR,1,1252)

... with the following...

(DT_STR,50,1252)NULL(DT_STR,50,1252)

hth,

Tim

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

hal9000-869914 SSC Enthusiast Points: 169 More actions · Answer 4

So, tested it with a simple staging table one varchar(50) column with just two values, one empty value and one date as varchar. Import into a table with one date column with derived column expression and data conversion to date. It works :-(. So it seems that there is some invalid data in my staging table. At the moment no idea how to fix this.

hal9000-869914 SSC Enthusiast Points: 169 More actions · Answer 5

Next step was to import the single date column of the flat file into this simple staging table. Loaded the staging data over derived colun and data conversion into the simple base table. It works again. I am getting crazy...:w00t:

Tim Mitchell SSCoach Points: 15681 More actions · Answer 6

If you suspect bad data in your staging table, check to see if there are any non-null, non-empty values that are invalid dates:

SELECT DISTINCT [DateColumnName]

FROM [StagingTable]

WHERE [DateColumnName] IS NOT NULL

AND LEN([DateColumnName]) > 0

AND ISDATE([DateColumnName]) 1

This should return a list of distinct values that cannot be converted to a date.

hth,

Tim

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices