Trouble with StringToDate

[Obiwon666]

Hi,

Here my situation:

Input schema:
col_1 integer nullable
col_2 varchar nullable

Output schema:
col_1 integer nullable
col_2 date nullable

When i convert "col_2" into datatype 'Date' with the code "StringToDate(col_2,"%yyyy%mm%dd")", i have the warning "Conversion error calling conversion routine date_from_string data may have been lost ".
But i have this warning only if my column "col_2" contains null.

If this column contains no null value, i have no warning.

I do some test to do conversion only if col_2 is not null but i still have this warning.

How i can avoid this warning ? (is it human trouble or engine mistake)

[chulett]

I do some test to do conversion only if col_2 is not null but i still have this warning.

Show us.

[Obiwon666]

Code: Select all

If IsNotNull(Ln_in.col_2) Then StringToDate(Ln_in.col_2,"%yyyy%mm%dd") Else SetNull()

I found the error, col_2 contains "" but not the null value.

I use this code and it works.

Code: Select all

If IsNotNull(Ln_in.col_2) or Ln_in.col_2 ="" Then StringToDate(Ln_in.col_2,"%yyyy%mm%dd") Else SetNull()

So it was a human problem.

[chulett]

Ah, yes... an empty string is not the same as a null. You could probably combine those two checks into a single one for length=0 but what you've got is fine as well.

[Kryt0n]

Code: Select all

If IsNotNull(Ln_in.col_2) or Ln_in.col_2 ="" Then StringToDate(Ln_in.col_2,"%yyyy%mm%dd") Else SetNull()

So it was a human problem.

It does? To me, that pushes empty string to the StringToDate which was said to be the cause of the error...

[chulett]

That's true... it does seem a little backwards.

[ray.wurlod]

I guess the "=" should have been "<>".

Does Len(string) return 0 when string IS NULL, like it does in server jobs?

[chulett]

I made that assumption but have no way to test it.

[Obiwon666]

I make a mistake when i write the code.

Code: Select all

Good code :
If IsNull(Ln_in.col_2) Or Ln_in.col_2="" Then SetNull() Else  StringToDate(Ln_in.col_2,"%yyyy%mm%dd")

Yes when i use len function, i have 0.

Pb for my job it's why i have no null values. My column is nullable.
Anyway my warning problem is solved.

For your information:

I made a test to know what do len with a null value in a column, the results is a warning "APT_CombinedOperatorController,1: Field 'Col_2' from input dataset '0' is NULL. Record dropped. [transform/tfmop_functions.C:184]"
If I add "If IsNull(Ln_in.Col_2) Then SetNull() Else Len(Ln_in.Col_2)", i have no warning and no row dropped

[josejohny]

Check it out. If Trim(NullToEmpty(Ln_in.col_2))<>"" Then StringToDate(Ln_in.col_2,"%yyyy%mm%dd") else SetNull() .Simple..

[adityavinay]

I am glad its working,
Lets say your source column have a value , space and null coming then this should solve

If IsNull(Ln_in.col_2) Or Trim(Ln_in.col_2)='' Then SetNull() Else StringToDate(Ln_in.col_2,"%yyyy-%mm-%dd")