Date Conversion Warnings

[Deepakinstyle]

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

[DSguru2B]

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

[Deepakinstyle]

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

[DSguru2B]

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

Code: Select all

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

[Deepakinstyle]

Hi,

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

thx,
deepak

[chulett]

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

[Deepakinstyle]

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.

[ray.wurlod]

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.

[Deepakinstyle]

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

[Deepakinstyle]

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

[Deepakinstyle]

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)
;

[ravibabu]

Hay,

Use the below one.


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