convert char to date

[deva]

Hi
In Source I have two columns one is eff_month, eff_year. those data type is char.
I want to load in target as due_date (data type date) as month/year.

I am doing concadination using to_date. in oracle it is working but in data stage I am getting error.

Can any one help me how to do this rek.

Thanks in Advance

[sud]

Hi, in datastage you have to have year, month and date all three in a date. The error must be because of that.

[ArndW]

Please post what you tried in DataStage. You can concatenate the columns and then use the StringToDate() function.

[ArndW]

You posted this in the PX forum, but marked it as a Server job ... which is it?

[dspxlearn]

ArndW,

He don't have the day from the source. He just has month and year. Then how can he concatenate and use StringToDate() function without a day?

[ray.wurlod]

Code: Select all

StringToDate(InLink.Eff_Year : "-" : InLink.Eff_Month : "-01")

[dspxlearn]

Ray,

So, below code along with the date format masking, we will be getting all '01' for days right?

Code: Select all

StringToDate(InLink.Eff_Year : "-" : InLink.Eff_Month : "-01") 

How about the below code.I guess we will be getting only the month/year.

Code: Select all

MonthFromDate(StringToDate(InLink.Eff_Year : "-" : InLink.Eff_Month : "-01","%yyyy-%mm-%dd")) : '/' :YearFromDate(StringToDate(InLink.Eff_Year : "-" : InLink.Eff_Month : "-01","%yyyy-%mm-%dd")) 

Ofcourse, if the target is a text file and not the database.

[sud]

Ray,

So, below code along with the date format masking, we will be getting all '01' for days right?

Code: Select all

StringToDate(InLink.Eff_Year : "-" : InLink.Eff_Month : "-01") 

How about the below code.I guess we will be getting only the month/year.

Code: Select all

MonthFromDate(StringToDate(InLink.Eff_Year : "-" : InLink.Eff_Month : "-01","%yyyy-%mm-%dd")) : '/' :YearFromDate(StringToDate(InLink.Eff_Year : "-" : InLink.Eff_Month : "-01","%yyyy-%mm-%dd")) 

Ofcourse, if the target is a text file and not the database.

Very funny

InLink.Eff_Year and InLink.Eff_Month already has year and month. So if he just needs a string with year/moth, he can directly concatenate them.

[dspxlearn]

Sud,

Original poster wanted his target datatype to be of Date datatype. And i guess, we cannot load a value of 'date' datatype without a day in it.

[paddu]

Hi
In Source I have two columns one is eff_month, eff_year. those data type is char.
I want to load in target as due_date (data type date) as month/year.

I am doing concadination using to_date. in oracle it is working but in data stage I am getting error.

Can any one help me how to do this rek.

Thanks in Advance

why same post at two different locations

viewtopic.php?t=116304

[sud]

why same post at two different locations

viewtopic.php?t=116304

Sadly, some people REALLY take this site for granted.