data type change for date
Moderators: chulett, rschirm, roy
data type change for date
Hello,
My source data is like 7/5/2012 and I am trying to read it through sequential file where it complains that MM should be two digits there is only one digit for month.
So I read the records as varchar in the sequential file and then used a transformer to change the data type from string to date and gave the format.
Now the transformer throws a warning saying the MM has fewer characters and at the end I cannot see the date but only ******** can be seen.
Please suggest how do I change the date to 07/05/2012 to get rid of the warning and see the records properly or else is there any other way to make it work?
Thank you!
My source data is like 7/5/2012 and I am trying to read it through sequential file where it complains that MM should be two digits there is only one digit for month.
So I read the records as varchar in the sequential file and then used a transformer to change the data type from string to date and gave the format.
Now the transformer throws a warning saying the MM has fewer characters and at the end I cannot see the date but only ******** can be seen.
Please suggest how do I change the date to 07/05/2012 to get rid of the warning and see the records properly or else is there any other way to make it work?
Thank you!
Thanks,
Surya
Surya
First of all, the sequential file stage (import operator) can read a variable length date format such as 7/5/2012. You just need to provide the proper format mask.
Secondly, the *s indicate that you messed up the string to date conversion. Post your exact derivation expression if you want someone here to help you fix it.
Mike
Secondly, the *s indicate that you messed up the string to date conversion. Post your exact derivation expression if you want someone here to help you fix it.
Mike
The expression I gave is StringToDate(Out_Src.BRTH_DT,"%mm/%dd/%yyyy")
The warning which I see is Data string '4/2/2039' does not match format '%mm/%dd/%yyyy': the value for tag %mm has fewer characters than expected.
The records in the target look like ******* for the date field
Thank you!
The warning which I see is Data string '4/2/2039' does not match format '%mm/%dd/%yyyy': the value for tag %mm has fewer characters than expected.
The records in the target look like ******* for the date field
Thank you!
Thanks,
Surya
Surya
You've not chosen a correct format string, as provided in the Date and Time Formats section of the product documentation.
Regards,
Regards,
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.
Thank you Mike and Jwiles
I gave the format StringToDate(Out_Src.BRTH_DT,"%m/%d/%yyyy")
The birth date format I see in the output of sequential file is 7/4/2012, so I gave the above expression as per the document.
I think the character warning went away but a differnt warning is seen
Conversion error calling conversion routine date_from_string data may have been lost
Please suggest do I need to change anything
I gave the format StringToDate(Out_Src.BRTH_DT,"%m/%d/%yyyy")
The birth date format I see in the output of sequential file is 7/4/2012, so I gave the above expression as per the document.
I think the character warning went away but a differnt warning is seen
Conversion error calling conversion routine date_from_string data may have been lost
Please suggest do I need to change anything
Thanks,
Surya
Surya
That error is typically encountered when you attempt to convert an empty/null string or other invalid data. You should add some logic to handle empty and invalid data. Also, you may wish to examine the source data to know how clean (valid vs invalid values) it is.
The IsValid() function has been discussed many times in the forum here and is often used to check for valid dates prior to conversion.
Regards,
The IsValid() function has been discussed many times in the forum here and is often used to check for valid dates prior to conversion.
Regards,
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.
I believe that format string will only work for single digit months / days like the 7/4 example posted but will have issues with dates like 10/4 or 7/25 or 11/16. The link James posted shows not only the valid format tags but also the options that are valid for them. Check out the "s" noted as it "indicates a numeric month of year field in which values can contain leading spaces or zeroes and be one or two characters wide" and shows usage examples.
Try it with both the month and day tags.
Try it with both the month and day tags.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Thank you very much!
I used the below expression and the valid dates looks fine and I gave todays date for invalid dates.
If IsValid('date',StringToDate(Out_Src.BRTH_DT,"%m/%d/%yyyy")) =1 then StringToDate(Out_Src.BRTH_DT,"%m/%d/%yyyy") else StringToDate('12/6/2012',"%m/%d/%yyyy")
Data looks good in the target but the warnings did not go which says
Conversion error calling conversion routine date_from_string data may have been lost
I used the below expression and the valid dates looks fine and I gave todays date for invalid dates.
If IsValid('date',StringToDate(Out_Src.BRTH_DT,"%m/%d/%yyyy")) =1 then StringToDate(Out_Src.BRTH_DT,"%m/%d/%yyyy") else StringToDate('12/6/2012',"%m/%d/%yyyy")
Data looks good in the target but the warnings did not go which says
Conversion error calling conversion routine date_from_string data may have been lost
Thanks,
Surya
Surya
I would suggest the following:
To take that further with Craig's suggestion regarding format string options to allow leading spaces and zeros:
where svDefaultDate is a Date stage variable initialized with
Regards,
Code: Select all
If IsValid('date',Out_Src.BRTH_DT,"%m/%d/%yyyy") =1 then StringToDate(Out_Src.BRTH_DT,"%m/%d/%yyyy") else svDefaultDate
Code: Select all
If IsValid('date',Out_Src.BRTH_DT,"%(m,s)/%(d,s)/%yyyy") =1 then StringToDate(Out_Src.BRTH_DT,"%(m,s)/%(d,s)/%yyyy") else svDefaultDate
Code: Select all
StringToDate('12/6/2012',"%m/%d/%yyyy")
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.