data type change for date

suryadev · Post by **suryadev** » Thu Dec 06, 2012 9:01 am

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!

Mike · Post by **Mike** » Thu Dec 06, 2012 9:09 am

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

suryadev · Post by **suryadev** » Thu Dec 06, 2012 9:34 am

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!

jwiles · Post by **jwiles** » Thu Dec 06, 2012 9:39 am

You've not chosen a correct format string, as provided in the Date and Time Formats section of the product documentation.

Regards,

suryadev · Post by **suryadev** » Thu Dec 06, 2012 9:57 am

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

jwiles · Post by **jwiles** » Thu Dec 06, 2012 10:39 am

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,

chulett · Post by **chulett** » Thu Dec 06, 2012 10:46 am

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.

jwiles · Post by **jwiles** » Thu Dec 06, 2012 11:01 am

That format string will work with both single and double digit months and days (at least as tested in 8.7), however it would not accept leading spaces in either without the additional options.

Regards,

chulett · Post by **chulett** » Thu Dec 06, 2012 11:11 am

Then it must be something they've smartened up in later releases as I don't recall that being the case in the past.

suryadev · Post by **suryadev** » Thu Dec 06, 2012 11:17 am

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

Mike · Post by **Mike** » Thu Dec 06, 2012 11:53 am

You've misused the IsValid function. It should be used before a conversion, not after.

Mike

jwiles · Post by **jwiles** » Thu Dec 06, 2012 12:05 pm

I would suggest the following:

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

To take that further with Craig's suggestion regarding format string options to allow leading spaces and zeros:

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

where svDefaultDate is a Date stage variable initialized with

Code: Select all

StringToDate('12/6/2012',"%m/%d/%yyyy")

Regards,

suryadev · Post by **suryadev** » Thu Dec 13, 2012 3:06 pm

Its working, thank you