something about date
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 47
- Joined: Wed Apr 12, 2006 12:13 pm
something about date
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
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
sylvan rydes
Re: something about date
Use IF(ISNULL(InLink.ColumnName)) THEN @NULL ELSE InLink.ColumnName in the column derivation.sylvan_rydes wrote: Also if the field is nullable then how can we just leave the space empty in the column.
gateleys
-
- Participant
- Posts: 47
- Joined: Wed Apr 12, 2006 12:13 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.
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.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 47
- Joined: Wed Apr 12, 2006 12:13 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
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
sylvan rydes
-
- Participant
- Posts: 47
- Joined: Wed Apr 12, 2006 12:13 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?
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?
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 47
- Joined: Wed Apr 12, 2006 12:13 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
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
sylvan rydes
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.
click on me
I Think there is a format change problem. I am not familiar with Oracle so i cannot comment more on that.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 47
- Joined: Wed Apr 12, 2006 12:13 pm
-
- Premium Member
- Posts: 108
- Joined: Sat Feb 05, 2005 6:52 pm
- Location: US
-
- Participant
- Posts: 47
- Joined: Wed Apr 12, 2006 12:13 pm
Day 0 in the DS BASIC internal date world is 1967-12-31. Somehow you're getting to there in your manipulations.
Last edited by kcbland on Wed Apr 19, 2006 8:31 pm, edited 1 time in total.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle