regarding Date conversion
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 196
- Joined: Tue Nov 23, 2004 11:50 pm
- Location: Sydney (Australia)
regarding Date conversion
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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?
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?
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: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
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
There are several threads on the undocumented suppress_zero option:
viewtopic.php?t=92663&highlight=decimaltostring
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Re: regarding Date conversion
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.
Post an example of the date you're trying to convert if you want any more help.
3NF: Every non-key attribute must provide a fact about the key, the whole key, and nothing but the key. So help me Codd.
-
- Premium Member
- Posts: 196
- Joined: Tue Nov 23, 2004 11:50 pm
- Location: Sydney (Australia)
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
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
-
- Premium Member
- Posts: 196
- Joined: Tue Nov 23, 2004 11:50 pm
- Location: Sydney (Australia)
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 196
- Joined: Tue Nov 23, 2004 11:50 pm
- Location: Sydney (Australia)
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 196
- Joined: Tue Nov 23, 2004 11:50 pm
- Location: Sydney (Australia)
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
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
-
- Premium Member
- Posts: 196
- Joined: Tue Nov 23, 2004 11:50 pm
- Location: Sydney (Australia)
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: