DSXchange

Silly of me, to even ask this question. However, I am stuck in converting a Varchar(unicode) to a date.

I am doing a lookup on ID and pulling out Birthdate,Firstname, Lastname etc.

I see that 4 records are getting rejected with Birthdate("**********")

After a pain staking effort, I realised, there are two unmatched columns, which outputs this ("**********").

However, there is one other record, with a match also giving out the same result.(Note: this Id has duplicates)

Derivation: stringtodate(lnk.Birthdate,"%mm-%dd-%yyyy")

Any suggetions on how to get rid of this ("**********")??

Thanks!

Identify which dates don't match the format.

Is your incoming date format mm-dd-yyyy without any leading or trailing spaces?

forgot to mention, My incoming data is in the format mm/dd/yyyy.

Also, I used

If IsNull(DSLink12.BIRTH_DT) Then "0000-00-00" Else StringToDate(UStringToString(DSLink12.BIRTH_DT[1,10]),"%mm/%dd/%yyyy") but no change.

If your input is a flat file than IsNull() will not work. Check for spaces as well.

My source is not a flat file. Its a DataSet. And even after trimming it still gives me the same result.

Job Design:


DS


DS---->LKP---->Trnsfrmr---->Seq File

Performing a lookup based on a ID. Which found no match for 3 ID's And since I am using a lookup, will output Nulls for those records.

Issue: With Date Field.[Source Format- mm/dd/yyyy)

Source(Varchar-Unicode)---->Target(Date)

Using the following derivation:

StringToDate(UStringToString(Trim(DSLink12.BIRTH_DT)) ,"%mm-%dd-%yyyy")


Transformer_45,1: Numeric string expected of the appropriate decimal precision . Use default value.Numeric string expected of the appropriate decimal precision . Use default value.

Transformer_45,0: Conversion error calling conversion routine date_from_string data may have been lost

Transformer_45,0: Conversion error calling conversion routine date_from_ustring data may have been lost


Peek_56,0: BIRTH_DT:**********
Peek_56,0: BIRTH_DT:**********
Peek_56,0: BIRTH_DT:**********

If I use just StringToDate(DSLink12.BIRTH_DT,"%mm-%dd-%yyyy") I get the below output.

Peek_56,1: BIRTH_DT:1992-02-21
Peek_56,1: BIRTH_DT:1959-12-21
Peek_56,1: BIRTH_DT:**********
Peek_56,1: BIRTH_DT:1989-12-12
Peek_56,1: BIRTH_DT:1963-11-11
Peek_56,1: BIRTH_DT:1960-08-28
Peek_56,1: BIRTH_DT:1948-07-27

I need all the records as I have to insert them as new records into the table and update the existing one's.

Any help will be appreciated. Thanks!!

"0000-00-00" is neither a valid date nor a valid string representation of a date.

Have you defined that as your representation for NULL in your sequential file output?

Mike

for handle null use NullToValue and define high date like 9999-12-31 to date in case null. also validate the incoming string whether it is really coming in format you mentioned %mm-%dd-%yyyy.

Thanks everyone. I had to remove the Unicode extension and then used the following derivation. Now it gets me the deisred output.

If IsValid('Date',StringToDate(DSLink12.BIRTH_DT,"%mm/%dd/%yyyy")) Then StringToDate(DSLink12.BIRTH_DT,"%mm/%dd/%yyyy") Else SetNull()