Date Conversion Warnings
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 47
- Joined: Wed Apr 18, 2007 4:49 am
Date Conversion Warnings
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
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
-
- Participant
- Posts: 47
- Joined: Wed Apr 18, 2007 4:49 am
Date Conversion warnings
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
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
Code: Select all
If IsNull(Caldt) then handle_null(Caldt, StringToDate("12/31/9999","%mm/%dd/%yyyy") else StringToDate(Caldt,"%mm/%dd/%yyyy")
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 47
- Joined: Wed Apr 18, 2007 4:49 am
-
- Participant
- Posts: 47
- Joined: Wed Apr 18, 2007 4:49 am
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 47
- Joined: Wed Apr 18, 2007 4:49 am
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
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
-
- Participant
- Posts: 47
- Joined: Wed Apr 18, 2007 4:49 am
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
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
-
- Participant
- Posts: 47
- Joined: Wed Apr 18, 2007 4:49 am