Data conversion

ntr · Post by **ntr** » Tue Nov 22, 2011 2:43 pm

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

kogads · Post by **kogads** » Tue Nov 22, 2011 3:29 pm

Can you please give your i/p and o/p formats..The question is not clear

chulett · Post by **chulett** » Tue Nov 22, 2011 9:48 pm

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.

ntr · Post by **ntr** » Wed Nov 23, 2011 7:36 pm

Hi ,

These are coming on the same column.
As 2 records.
so i want to load it into the oracle database.

ray.wurlod · Post by **ray.wurlod** » Wed Nov 23, 2011 7:55 pm

You're going to need some If..Then..Else logic to differentiate the formats, and apply the appropriate conversion in each case.

ntr · Post by **ntr** » Wed Nov 23, 2011 8:28 pm

Hi,
Can you give an example i am new to datastage

chulett · Post by **chulett** » Wed Nov 23, 2011 8:31 pm

Are those the only two formats or have you seen others in that field?

ntr · Post by **ntr** » Wed Nov 23, 2011 8:37 pm

Hi Craig,

I think in future 10/21/2011

will also come

SURA · Post by **SURA** » Wed Nov 23, 2011 9:34 pm

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

ray.wurlod · Post by **ray.wurlod** » Wed Nov 23, 2011 9:49 pm

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")

ntr · Post by **ntr** » Wed Nov 23, 2011 9:57 pm

Hi,
I.e mm/dd/yyyy format

ray.wurlod · Post by **ray.wurlod** » Wed Nov 23, 2011 11:16 pm

No, date format, for example 000001101101001000101010100010110101

ntr · Post by **ntr** » Fri Dec 02, 2011 5:37 pm

Hi to all,
Please give solution for this.Still i am not able to get the solution.

Thanks

qt_ky · Post by **qt_ky** » Fri Dec 02, 2011 9:21 pm

Solution has been very clearly given above. What part of it do you have a question about?