Page 1 of 1
Data conversion
Posted: Tue Nov 22, 2011 2:43 pm
by ntr
Hi,
I am havin a date column of varchar .
I want to change to Date format.
my data : 3/21/2011
0001-02-02.
how can i change to date format .
Can any one have any idea?.
Thanjs & Regards
Posted: Tue Nov 22, 2011 3:29 pm
by kogads
Can you please give your i/p and o/p formats..The question is not clear
Posted: Tue Nov 22, 2011 9:48 pm
by chulett
There is no "output format" here if the target is a date. So the question remains, are those two examples of what can come in as your date string?
3/21/2011
0001-02-02
So, both m/d/y & y-m-d formats in the same field? If not then please explain what you will be getting with additional examples, please.
Posted: Wed Nov 23, 2011 7:36 pm
by ntr
Hi ,
These are coming on the same column.
As 2 records.
so i want to load it into the oracle database.
Posted: Wed Nov 23, 2011 7:55 pm
by ray.wurlod
You're going to need some If..Then..Else logic to differentiate the formats, and apply the appropriate conversion in each case.
Posted: Wed Nov 23, 2011 8:28 pm
by ntr
Hi,
Can you give an example i am new to datastage
Posted: Wed Nov 23, 2011 8:31 pm
by chulett
Are those the only two formats or have you seen others in that field?
Posted: Wed Nov 23, 2011 8:37 pm
by ntr
Hi Craig,
I think in future 10/21/2011
will also come
Posted: Wed Nov 23, 2011 9:34 pm
by SURA
10/10/2011
In this case which is date and which is month?
Handling date column is always tricky and source should say how it is!!
DS User
Posted: Wed Nov 23, 2011 9:49 pm
by ray.wurlod
The If..Then..Else construct determines which of the two formats is used in the current row. It is sufficient to test the first four characters being numeric (which indicates YYYY-MM-DD format in your case).
Code: Select all
If Num(InLink.TheString[1,4]) Then StringToDate(InLink.TheString,"%yyyy-%mm-%dd") Else StringToDate(InLink.TheString,"%m/%d/%yyyy")
Posted: Wed Nov 23, 2011 9:57 pm
by ntr
Hi,
I.e mm/dd/yyyy format
Posted: Wed Nov 23, 2011 11:16 pm
by ray.wurlod
No, date format, for example 000001101101001000101010100010110101
Posted: Fri Dec 02, 2011 5:37 pm
by ntr
Hi to all,
Please give solution for this.Still i am not able to get the solution.
Thanks
Posted: Fri Dec 02, 2011 9:21 pm
by qt_ky
Solution has been very clearly given above. What part of it do you have a question about?