regarding Date conversion

Nripendra Chand · Post by **Nripendra Chand** » Tue Sep 06, 2005 6:48 am

Hi,

in px decimal to date function is not available. so I'm changing the decimal to string first and then using stringtodate funtion to get the date output. Input data is in decimal form.
But in output it's showing me ********. I've specified date as the data type in the output column.
What is the reason?

Regards,
Nripendra

ray.wurlod · Post by **ray.wurlod** » Tue Sep 06, 2005 4:07 pm

Show us exactly what you did, and that may be an easier question to answer.
Did you build this incrementally, verifying that your conversion to string is successful? If so, what does the string look like? That having been done, does that correspond to the date picture you are currently using?

vmcburney · Post by **vmcburney** » Tue Sep 06, 2005 5:51 pm

I keep a test job around that has every data type as input and output. I use it to test parallel transformation and modify functions such as these. It lets me output the final result and interim values such as the value produced by DecimaltoString. You may need to add formatting to your DecimaltoString such as fix_zero or suppress_zero to stop pesky leading zeros and decimal points from being added that may cause problems in the string to date conversion.

There are several threads on the undocumented suppress_zero option:
viewtopic.php?t=92663&highlight=decimaltostring

kumar_s · Post by **kumar_s** » Tue Sep 06, 2005 9:39 pm

Hi,
Data Length also dose the mater.
Probably date field doesnt require the length to be specified. If specified less than the required even during conversion, it happens.

regards
kumar

battaliou · Post by **battaliou** » Wed Sep 07, 2005 5:43 am

Usually you gets *'s when the date is null or in the wrong format. I hope you are using varchar(11) rather than char as this will introduce a null filler into your data unless you've set $APT_STRING_PADCHAR= space

Post an example of the date you're trying to convert if you want any more help.

Nripendra Chand · Post by **Nripendra Chand** » Wed Sep 07, 2005 10:50 pm

input record is 20050812. it's datatype is decimal(8). I'm converting it to a string using decimaltostring fn and assigning it to a stage variable named "svr". Data type of the stage variable is varchar(8). Then in the column derivation I'm using following funtion:
StringToDate(left(svr,4):"-":right(left(svr,2),2):"-":right(left(svr,8),2),"%yyyy-%mm-%dd")
The output column is having the data type date(10). After running the job output data is coming as ***********.

-Nripendra

Nripendra Chand · Post by **Nripendra Chand** » Wed Sep 07, 2005 10:53 pm

in my last reply length has been replaced by emotions. input data type is decimal and length is 8. the length of stage variable is 8. and the length of the output date is 10.

-Nripendra

ray.wurlod · Post by **ray.wurlod** » Wed Sep 07, 2005 11:38 pm

Look a bit more closely at the right(left(svr,2),2) part of your expression. Consider using the Substring function for the middle component.

Nripendra Chand · Post by **Nripendra Chand** » Thu Sep 08, 2005 1:31 am

Hi Ray,

I can't see any substring fn in px. In px "left" and "right" fn are available for extraction purpose. If i assign the output col as char(10), the output is coming properly (2005-08-12). I checked the length of the derivation also. Length is coming as 10.

-Nripendra

ray.wurlod · Post by **ray.wurlod** » Thu Sep 08, 2005 1:38 am

Sorry, should have been clearer. Substring from the expression editor's operator menu. It's represented in the expression using square brackets.

Nripendra Chand · Post by **Nripendra Chand** » Thu Sep 08, 2005 2:04 am

But if i use char(10) as data type in the output column, then output is coming correct. So do you think there is any issue with left and right fn?

-Nripendra

richdhan · Post by **richdhan** » Fri Sep 09, 2005 2:54 am

Hi Nripendra Chand,

Try to output the value of the stage variable without doing the StringToDate Conversion. You will find that there will a leading space to the string and the last character truncated(A weird behaviour)

I hope you are using DecimalToString(input_column) as the derivation for stage variable. Use Trim(DecimalToString(input_column)).

Use the following output to do the StringtoDate conversion. It should be through.

HTH
--Rich

Nripendra Chand · Post by **Nripendra Chand** » Sun Sep 11, 2005 11:53 pm

Hi richdhan,

I did this as well but it's not working. One thing I've analysed. Suppose input is 20041010 (decimal value). While converting it into a string, output is coming as "20041010.". When I tried to use left to extract only left 8 most characters, it is giving "2004101.". It is removing the 2nd right most char and putting ".". I'm really surprised with this. What should i do to remove the "."?

Regards,
Nripendra

ray.wurlod · Post by **ray.wurlod** » Mon Sep 12, 2005 12:36 am

Do you have a leading space (reserved for the sign)? Check with a Peek stage or similar.

richdhan · Post by **richdhan** » Mon Sep 12, 2005 1:28 am

Hi Nripendra Chand,

Use the following conversion. You should be through.

Trim(Field(DecimalToString(INPUT_COLUMN),".",1))

HTH
--Rich

DSXchange

regarding Date conversion

regarding Date conversion

Re: regarding Date conversion