Replace a bad date with a Null

0
votes

I am working on a project with our HR department that counts the number of Temps that have been hired over a specific time.

I am importing data from an Excel Spreadsheet that is being supplied from our SAP system in a monthly automated Export. The [TempHireDate] column has 00/00/0000 instead of a NULL in the Cells that a date was not required. I want to run a derived column that strips out the 00/00/0000 and replaces it with a NULL value as I import it to the table.

I believe a derived column is the answer but i am having difficulty getting any of the standard expressions to work. I thought the REPLACE expression would work but I don't think I am doing it right.

REPLACE(00/00/0000,[TempHireDate],NULL)

I did a search and found some other examples but could not get them to work either.

(Date_To_Check == "00/00/0000" || (DT_Date)Date_To_Check < (DT_DATE)"1753-1-1") ? NULL(DT_DATE) : (DT_Date)Date_To_Check

and

ISNULL([Column 0]) || LEN(TRIM((DT_WSTR,10)[Column 0])) == 0 || [Column 0]==”00/00/0000” NULL(DT_DATE) : (DT_DBDATE)((DT_WSTR,10)[Column 0])

Neither worked and again I think I was doing something wrong.

Does anyone have any suggestions on what I can do or how I can get these expressions to work?

2
sqlssisexpression
Are you doing this in Excel or SQL code? CASE WHEN DT_DATE = '00/00/0000' THEN NULL ELSE DT_DATE END - Petio Ivanov
I am importing from Excel to an SQL staging table. I already have a package that has a few derived columns in it making transformations. I just need to add this one transformation in order to make the new count that HR wants to see. I went to the source but this date column is hard coded in the master (SAP) system and can not be altered. Therefor my only solution is to strip out the 00/00/0000 when the data is imported into the staging table. - Brian D. Brubaker
You could just run an update on the table after you import the data: UPDATE staging_table SET TempHireDate = NULL where TempHireDate = '00/00/0000' - Petio Ivanov
I think you were on the right track with REPLACE, but it should probably be REPLACE([TempHireDate],"00/00/0000",NULL), but this only works with a specific data type WSTR. You could also try TempHireDate== "00/00/0000"? NULL(DT_WSTR):TempHireDate. In any case you have to keep in mind whether the data types you haev are compatible with the functions you are using. - Petio Ivanov
Neither of the above Expressions worked. I double checked the import and the column data type is DT_WSTR. The reason I want to use the Derived column is because it needs to be an automated process every month. They dont want anyone to touch it once it starts running. I figured the Derived column would be the easiest way to accomplish this. - Brian D. Brubaker

2 Answers

1
votes

add as new column

[TempHireDate] == "00/00/0000" ? NULL(DT_WSTR,10) : [TempHireDate]
1
votes
SELECT NullIf(TempHireDate, '00/00/0000')
FROM   your_table