DSXchange

Hi,

I have a requirement where i need to convert varchar to date.
I am using the conversion function stringtodate as below

StringToDate(Caldt,"%mm/%dd/%yyyy")
output date format should be mm/dd/yyyy

data is loading successfully but still am getting warnings like "APT_CombinedOperatorController(1),1: Conversion error calling conversion routine date_from_string data may have been lost."

i have added a peek stage to transformer , there am getting "Peek_73,1: CALENDAT_DT:********** IBT_MAT_DT:********** IBT_AS_OF_DT:********** IBT_SETTL_DT:********** "

I have null values for only MAT_DT. Let me know what might be the problem. If it is with null, how to handle it?
I'm new to parallel edition. Need some guidance.


Thanks,
Deepak

Check for null values using IsNull() function. Send only the not null values to the StringToDate() function.

Hi DsGuru,

For non null values i will use conversion function for null values how to handle them as datatype in target is date
how to pass null values there.

thx,
deepak

Is it a nullable column? If yes then pass as is else get the alternative or default date for null from your business and use handle_null() function (search for more specifics). Something like

Hi,

Is handle_null is a function is datastage?
it is not accepting

thx,
deepak

What is 'not accepting' it? Handle_Null() must be used in a Modify stage from what I understand.

Hi,

I am using this function to handle null values in date conversion from string to date.

If IsNull(Caldt) then handle_null(Caldt, StringToDate("12/31/9999","%mm/%dd/%yyyy") else StringToDate(Caldt,"%mm/%dd/%yyyy")

I am getting it in red color. what is modify stage? Let me know I am new to Parallel edition.

You should use the expression editor to build expressions, rather than typing them in. That way you would get your input column names properly specified - that is, qualified with the input link name.

Hi,

I am using this code in Modify stage.

IBT_MAT_DT:DATE = date_from_string [%mm/%dd/%yyyy] MATDT

IBT_MAT_DT->new column name
DATE ->new type
date_from_string [%mm/%dd/%yyyy] -> new format
MATDT -> Old column name

am getting error. let me know the correct format and how to handle null values here. I am not supposed to put any default values for Null as the column in the target table nullable. so i have null if the input is null

thx,
deepak

Hi,

I am using this code in Modify stage.

IBT_MAT_DT:DATE = date_from_string [%mm/%dd/%yyyy] MATDT

IBT_MAT_DT->new column name
DATE ->new type
date_from_string [%mm/%dd/%yyyy] -> new format
MATDT -> Old column name

am getting error. let me know the correct format and how to handle null values here. I am not supposed to put any default values for Null as the column in the target table nullable. so i have to pass null if the input is null

thx,
deepak

Error Message

main_program: Error parsing modify adapter: Error in binding: Error in destination field selector: Expected integer, got: ":"
Expected destination field selector, got: ")"; input:
IBT_MAT_DT[:DATE] = date_from_string [%mm/%dd/%yyyy] (MATDT)
;

Hay,

Use the below one.


IBT_MAT_DT = date_from_string [%mm/%dd/%yyyy] MATDT