Replace 1/1/1900 with space / null using SSIs derived columns

Question

Post reply

Replace 1/1/1900 with space / null using SSIs derived columns

Madhuchhanda Kundu

Old Hand

Points: 360
More actions
February 23, 2010 at 3:48 pm

#145511

I used Ltrim([enrichment_dt])== "1/1/1900" ? ""
does not work .
Can anybody please help ?thanks in advance.

Viewing 10 posts - 1 through 10 (of 10 total)

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

What is the desired datatype of the end-result? Also what is the datasource?

CEWII

Madhuchhanda Kundu Old Hand Points: 360 More actions · Answer 2

the end data type is smalldate , reading from text file.

thanks

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

Space is not valid for that datatype. you could replace it with a NULL, or you could change the datatype to a character type. But space isn't going to insert into a table.

CEWII

Madhuchhanda Kundu Old Hand Points: 360 More actions · Answer 4

Madhuchhanda Kundu

Old Hand

Points: 360

February 24, 2010 at 2:01 pm

#1124583

Replacing the space with a null did not work either

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

Ok, why don't you explain the whole process then because this trying to fix the problem we can see with the microscope isn't working..

CEWII

Madhuchhanda Kundu Old Hand Points: 360 More actions · Answer 6

text file comes with default date 1/1/1900 . I tried in derived column before converting in to date as [column1] =="1/1/1900" ? Null or

[column1] =="1/1/1900" ? "" before data conversion to date,

tried after data conversion to date as

[column1] =="1/1/1900" ? (DT_date) Null

does not work , either way , accomplished by using update at the end of loading in transact_ SQl but that is not our standards.

Please advice.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 7

Why don't you try:

[column1] =="1/1/1900" ? [column1] : NULL(DT_DBTIMESTAMP)

CEWII

wschampheleer SSCertifiable Points: 5504 More actions · Answer 8

Elliott W (2/24/2010)
Why don't you try:
[column1] =="1/1/1900" ? [column1] : NULL(DT_DBTIMESTAMP)
CEWII

You need to reverse the 2nd and 3rd operand.

Also, I assume that col1 is of datatype DT_STR or DT_WSTR, so you will have to do an extra cast:

col1 == "1/1/1900" ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)col1

The output needs to be stored in a new column (of data type DT_DBTIMESTAMP.

Regards,

Willem
http://wschampheleer.wordpress.com[/url]

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 9

I agree, the flip is necessary. Sorry, it was late.

CEWII

Replace 1/1/1900 with space / null using SSIs derived columns

Cookies on SQLServerCentral