something about date

sylvan_rydes · Post by **sylvan_rydes** » Wed Apr 19, 2006 1:13 pm

Hi All,

I got something interesting while creating a job. I had one constant input date which is 12/31/2999. When I connect to output link and run the job. I see 12/31/1967 instead of 12/31/2999.

Also if the field is nullable then how can we just leave the space empty in the column.

Any suggestion. Thanks.

Sylvan

DSguru2B · Post by **DSguru2B** » Wed Apr 19, 2006 1:27 pm

what is your source and target?
As for your second question, check for null in transformer and insert literal 'NULL' or some other value, as you wish.

gateleys · Post by **gateleys** » Wed Apr 19, 2006 1:31 pm

sylvan_rydes wrote: Also if the field is nullable then how can we just leave the space empty in the column.

Use IF(ISNULL(InLink.ColumnName)) THEN @NULL ELSE InLink.ColumnName in the column derivation.

gateleys

sylvan_rydes · Post by **sylvan_rydes** » Wed Apr 19, 2006 1:35 pm

Thanks for the reply

My source is Oracle OCI8 and same is target.

Sylvan

DSguru2B · Post by **DSguru2B** » Wed Apr 19, 2006 2:02 pm

are you doing some kind of conversions. Especially date conversions.
From the looks of it, you are trying to ICONV and then OCONV.
The ICONV is not set up correctly and hence its spitting out a zero. The Oconv takes that zero and turns it into 12/31/1967 as that the translation of 0 internally by Datastage.

sylvan_rydes · Post by **sylvan_rydes** » Wed Apr 19, 2006 2:18 pm

Hi Jules,

Thanks a lot for the reply. Well I am just trying to pass a string 12/31/2999 to date but as it is not valid So it is returning junk values. I also used the following function:

Oconv(Iconv('12/31/2999', 'D/MDY[2,2,4]'), 'D/MDY[2,2,4]')
but still it says input invalid.

But if I use output type as character then job is done successfully.

Now can you help me.

Sylvan

gateleys · Post by **gateleys** » Wed Apr 19, 2006 2:22 pm

What is the data type in your target?

gateleys

sylvan_rydes · Post by **sylvan_rydes** » Wed Apr 19, 2006 2:26 pm

It is Date type and I don't want to change it.

Thanks gateleys

DSguru2B · Post by **DSguru2B** » Wed Apr 19, 2006 2:34 pm

IF you are pulling for an oracle table and able to pull that kind of date successfully, then how come you are not able to load it.
Are you sure your immediate source is Oracle table. Or is it a flat file.
If it is a flat file then you have to convert it to a format that Oracle excepts.
I am a bit confused here. Can you clearly state your design, and what transformations you are doing and how?

sylvan_rydes · Post by **sylvan_rydes** » Wed Apr 19, 2006 2:39 pm

Thanks for the reply.

I have got two options. One is start date and other one is end date. Start date could be anything or null and end date is 12/31/2999. Both are constant. And I need to fill them in target. As I told you only the startdate is from input OCI8 which could be NULL. And Enddate is constant.

I guess this makes is clear.

Sylvan

DSguru2B · Post by **DSguru2B** » Wed Apr 19, 2006 2:53 pm

Here is what i found
click on me

I Think there is a format change problem. I am not familiar with Oracle so i cannot comment more on that.

sylvan_rydes · Post by **sylvan_rydes** » Wed Apr 19, 2006 3:09 pm

thanks a lot Jules.

diamondabhi · Post by **diamondabhi** » Wed Apr 19, 2006 3:11 pm

Who is Jules and ehy r u tahnking him?

sylvan_rydes · Post by **sylvan_rydes** » Wed Apr 19, 2006 3:24 pm

My misunderstanding. I had seen the name not the quote.

kcbland · Post by **kcbland** » Wed Apr 19, 2006 4:08 pm

Day 0 in the DS BASIC internal date world is 1967-12-31. Somehow you're getting to there in your manipulations.

DSXchange

something about date

something about date

Re: something about date