convert MM/DD/YYYY IN TO DD-MON-YYYY
Moderators: chulett, rschirm, roy
convert MM/DD/YYYY IN TO DD-MON-YYYY
Hai,
I want to convert date like this MM/DD/YYYY IN TO DD-MON-YYYY.
My Source is Oci Stage and My Target also Oci Stage. My Date Column
Name is Earned Date in source (VARCHAR 38) NULL - YES, In My Target
Column Name is Agg_Earned_date(DATE ) NULL -YES.
I have written query in transformer like this
Oconv(Iconv(LNK_STAGE_PERIPHERALUS_SURVEY_JRNL_REV_EXT.EARNED_DT,"DMDY
[2,2,4]":@VM:"MCN"),"D-DMY[2,A3,4]")
when i run the job, i got the result in sequential file(31-DEC-2006). But
when i check in to the Oci stage, i did not get the result. I don't know
where is the problem. Even i have tried to change the Target Column
Data type like Varchar, TimeStamp like this. But whenever i have view
the data, the corresponding column(AGG_EARNED_DATE), it didn't show
any data.
That means that column having no data. I don't know where is the
problem. Would you please help me? How can i solve the problem. If
any problem in my query, please send me the correct query.
with regards,
prabu
I want to convert date like this MM/DD/YYYY IN TO DD-MON-YYYY.
My Source is Oci Stage and My Target also Oci Stage. My Date Column
Name is Earned Date in source (VARCHAR 38) NULL - YES, In My Target
Column Name is Agg_Earned_date(DATE ) NULL -YES.
I have written query in transformer like this
Oconv(Iconv(LNK_STAGE_PERIPHERALUS_SURVEY_JRNL_REV_EXT.EARNED_DT,"DMDY
[2,2,4]":@VM:"MCN"),"D-DMY[2,A3,4]")
when i run the job, i got the result in sequential file(31-DEC-2006). But
when i check in to the Oci stage, i did not get the result. I don't know
where is the problem. Even i have tried to change the Target Column
Data type like Varchar, TimeStamp like this. But whenever i have view
the data, the corresponding column(AGG_EARNED_DATE), it didn't show
any data.
That means that column having no data. I don't know where is the
problem. Would you please help me? How can i solve the problem. If
any problem in my query, please send me the correct query.
with regards,
prabu
Re: convert MM/DD/YYYY IN TO DD-MON-YYYY
Where did you try changing the datatype? In the table or within the OCI stage? Also, Oracle accepts date in the format YYYY-MM-DD unless you have set your default value otherwise. Try changing the datatype in the table to varchar and see if that works.praburaj wrote: Even i have tried to change the Target Column
Data type like Varchar, TimeStamp like this.
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
The OCI stages require the 'Timestamp' datatype to be in the following format:
YYYY-MM-DD HH24:MI:SS
As can be seen by the sql the stage generates. If you don't need to time portion that a DATE field can carry, then just tack on a zero time to the end of your date: " 00:00:00".
YYYY-MM-DD HH24:MI:SS
As can be seen by the sql the stage generates. If you don't need to time portion that a DATE field can carry, then just tack on a zero time to the end of your date: " 00:00:00".
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Is your target a Date field or Timestamp field ?
If date the do this
This will convert your outpu to YYYY-MM-DD which oracle accepts.
If date the do this
Code: Select all
OCONV(ICONV(in.date, "D/MDY[2,2,4]"), "D-YMD[4,2,2]")
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
People, Oracle will accept any date format - as long as the proper TO_DATE mask goes along with it. And unless you are a fan of user-defined sql places where it ain't needed, I'd suggest you stick with the format that matches the sql the stage will auto-generate. ![Wink :wink:](./images/smilies/icon_wink.gif)
![Wink :wink:](./images/smilies/icon_wink.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Guru, Is your code convert the date format MM/DD/YYY(12/03/2006( TO
DD-MON-YYYY(03-DEC-2006) like this? pls clear my doubt..
And I have one more doubt. I need date format like this 03-DEC-2006
in OCI stage. Is it possible to change the date format inside the OCI
STAGE like this ( To_date(Mycolumn DD-MON-YYYY). I am going to give
this condition in USER DEFINED SQL. Because,Whenever i am trying to
view the data, i am getting the Null Value for the correponding column.
Plz, clear My doubt. If it is wrong, how can i do that?
Can I use the same query in transformer:
Oconv(Iconv
(LNK_STAGE_PERIPHERALUS_SURVEY_JRNL_REV_EXT.EARNED_DT,"DMDY
[2,2,4]":@VM:"MCN"),"D-DMY[2,A3,4]") with user_defined Sql condition.
Plz help me![Rolling Eyes :roll:](./images/smilies/icon_rolleyes.gif)
DD-MON-YYYY(03-DEC-2006) like this? pls clear my doubt..
And I have one more doubt. I need date format like this 03-DEC-2006
in OCI stage. Is it possible to change the date format inside the OCI
STAGE like this ( To_date(Mycolumn DD-MON-YYYY). I am going to give
this condition in USER DEFINED SQL. Because,Whenever i am trying to
view the data, i am getting the Null Value for the correponding column.
Plz, clear My doubt. If it is wrong, how can i do that?
Can I use the same query in transformer:
Oconv(Iconv
(LNK_STAGE_PERIPHERALUS_SURVEY_JRNL_REV_EXT.EARNED_DT,"DMDY
[2,2,4]":@VM:"MCN"),"D-DMY[2,A3,4]") with user_defined Sql condition.
Plz help me
![Rolling Eyes :roll:](./images/smilies/icon_rolleyes.gif)
No, you don't "need" the date in that format.praburaj wrote:I need date format like this 03-DEC-2006 in OCI stage. Is it possible to change the date format inside the OCI STAGE like this ( To_date(Mycolumn DD-MON-YYYY). I am going to give this condition in USER DEFINED SQL.
No, that's not what the TO_DATE needs to look like.
No, you don't "need" user defined sql for this - in fact you do yourself a great disservice by using it when there's absolutely no need to do so.
![Idea :idea:](./images/smilies/icon_idea.gif)
Read the OCI stage docs or simply examine the sql the stage generates for a Timestamp datatype. As I've already meantioned, it is expecting you to supply data in the following format:
YYYY-MM-DD HH24:MI:SS
A standard ISO timestamp. Convert your date in the Transformer using the following Conv pair, first taking it to Internal format and then bringing it back out in External format in the format desired:
Code: Select all
OConv(IConv(Link.Column, "D"),"D-YMD[4,2,2]"):" 00:00:00"
In my opinion, it's best to always treat an Oracle DATE using a full Timestamp value - this way you have explicit control over the 'time' portion that each date optionally carries in Oracle. If you want 'no time' then ensure that by passing a zero time as shown above.
The resulting Timestamp value can be sent to the OCI stage and with Generated SQL will load just fine.
ps. What are you using to post these messages? Everything is double-spaced with hard returns at the end of every line. There's no need for that and makes reading and responding to your posts a pain.
![Evil or Very Mad :evil:](./images/smilies/icon_evil.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
I have tried Even this code also.
OConv(IConv(Link.Column, "D"),"D-YMD[4,2,2]"):" 00:00:00" . Still I am getting Null value for that corresponding column. My target is OCI Stage.
Column Name : Earned_date Datatype: TimeStamp Length :20.
Source Date Format : 21/31/2006 data type : varchar
Output Need : 31-DEC-2006 data type : date
I have tried as far as possible. But still i am getting null value.
How can i solve that. Plz help me.![Rolling Eyes :roll:](./images/smilies/icon_rolleyes.gif)
OConv(IConv(Link.Column, "D"),"D-YMD[4,2,2]"):" 00:00:00" . Still I am getting Null value for that corresponding column. My target is OCI Stage.
Column Name : Earned_date Datatype: TimeStamp Length :20.
Source Date Format : 21/31/2006 data type : varchar
Output Need : 31-DEC-2006 data type : date
I have tried as far as possible. But still i am getting null value.
How can i solve that. Plz help me.
![Rolling Eyes :roll:](./images/smilies/icon_rolleyes.gif)
How can you solve it? By telling us whatever it is that you are not telling us that is causing this to fail.
There is nothing wrong with the derivation posted and it does work when used properly as I've done this very thing in thousands of jobs over the years. You need to triple-check everything you are doing, make no assumptions about what you've done or must have done correctly - check everything. As I noted, you'll also need to let the stage generate the sql for you, you're not doing any of that silly user-defined crap, are you? Are you getting any warnings in the job's log when it runs and gives you this 'null value'? There's definitely some key piece of information you're withholding. You need to figure it out, we can't from the wrong side of the glass.![Rolling Eyes :roll:](./images/smilies/icon_rolleyes.gif)
So, the big summary - the rest is up to you:
* Oracle DATE field
* OCI target stage
* Timestamp datatype in job
* Timestamp size: 38 Display size: 20
* Correct source field mapped to correct target field
* Derivation: OConv(IConv(Link.Column, "D"),"D-YMD[4,2,2]"):" 00:00:00"
* SQL generated by the stage, not user-defined
That is all you need. If this still doesn't work, make sure you come back with a warning or an error or some other reason why it doesn't work - otherwise all anyone can do is repeat everything that's already been said in this thread and ask you to check what you are doing again.
![Evil or Very Mad :evil:](./images/smilies/icon_evil.gif)
There is nothing wrong with the derivation posted and it does work when used properly as I've done this very thing in thousands of jobs over the years. You need to triple-check everything you are doing, make no assumptions about what you've done or must have done correctly - check everything. As I noted, you'll also need to let the stage generate the sql for you, you're not doing any of that silly user-defined crap, are you? Are you getting any warnings in the job's log when it runs and gives you this 'null value'? There's definitely some key piece of information you're withholding. You need to figure it out, we can't from the wrong side of the glass.
![Rolling Eyes :roll:](./images/smilies/icon_rolleyes.gif)
So, the big summary - the rest is up to you:
* Oracle DATE field
* OCI target stage
* Timestamp datatype in job
* Timestamp size: 38 Display size: 20
* Correct source field mapped to correct target field
* Derivation: OConv(IConv(Link.Column, "D"),"D-YMD[4,2,2]"):" 00:00:00"
* SQL generated by the stage, not user-defined
That is all you need. If this still doesn't work, make sure you come back with a warning or an error or some other reason why it doesn't work - otherwise all anyone can do is repeat everything that's already been said in this thread and ask you to check what you are doing again.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
All of the derivations will generate NULL if there is NULL in the source.
You need to do some detective work - perhaps using the debugger, perhaps using stage tracing - to determine in which line these NULL values are occurring.
I would also ask you to demonstrate that what you are getting really is NULL, not zero-length string (""). These are not the same thing.
Start by taking the OCI target stage out of the design - use a Sequential File stage, and determine those rows in which you do not get a timestamp in the column in question.
You need to do some detective work - perhaps using the debugger, perhaps using stage tracing - to determine in which line these NULL values are occurring.
I would also ask you to demonstrate that what you are getting really is NULL, not zero-length string (""). These are not the same thing.
Start by taking the OCI target stage out of the design - use a Sequential File stage, and determine those rows in which you do not get a timestamp in the column in question.
Last edited by ray.wurlod on Mon Dec 25, 2006 9:05 pm, edited 1 time in total.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Wow, lots of fun chit chat huh. I see craig had fun over the holidays
Praburaj, i think Craig cleared out your question that was referred to me. There isnt really much we can do from this side of the glass as Craig already pointed out to you. Follow the second last entry of Craig and post the results.
![Wink :wink:](./images/smilies/icon_wink.gif)
Praburaj, i think Craig cleared out your question that was referred to me. There isnt really much we can do from this side of the glass as Craig already pointed out to you. Follow the second last entry of Craig and post the results.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.