Date Conversion formats
Moderators: chulett, rschirm, roy
Completely different problem, and one we could have avoided if you'd mentioned right away this was for Oracle. However, not all that surprising.
For that you want YYYY-MM-DD HH24:MI:SS for the mask and a TO_DATE() function in the SQL using that mask. Set the datatype of the target field to Timestamp in your job and the generated SQL will automatically add that. Doing this means date conversions that always work and do not attempt to rely (as you seem to be doing) on the NLS_DATE format of the target database.
Oh, and yes that does mean you'll now need to convert the time portion as well, so it's time to investigate the use of the "MTS" option of IConv/OConv.
For that you want YYYY-MM-DD HH24:MI:SS for the mask and a TO_DATE() function in the SQL using that mask. Set the datatype of the target field to Timestamp in your job and the generated SQL will automatically add that. Doing this means date conversions that always work and do not attempt to rely (as you seem to be doing) on the NLS_DATE format of the target database.
Oh, and yes that does mean you'll now need to convert the time portion as well, so it's time to investigate the use of the "MTS" option of IConv/OConv.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
sorry so i modified it to this for the timestamp portion
OCONV(ICONV(Field(DSLink2.CreateDate," ",1,1),"D"),"D-DMY[2,A3,2] ") :" ": OCONV(Iconv(Field(DSLink2.CreateDate," ",2,2) , "MTS"),"MTS")
I have the datatype set to timestamp so your saying now
i have to modify the format from 31-OCT-2008
to 2008-10-31
?
OCONV(ICONV(Field(DSLink2.CreateDate," ",1,1),"D"),"D-DMY[2,A3,2] ") :" ": OCONV(Iconv(Field(DSLink2.CreateDate," ",2,2) , "MTS"),"MTS")
I have the datatype set to timestamp so your saying now
i have to modify the format from 31-OCT-2008
to 2008-10-31
?
Yes, as a 'best practice' for dealing with Oracle DATE fields I always treat them as Timestamps in the format I noted. So your goal is to transform the original string to one that looks like this:
"10/29/2008 8:31:00 AM" -> "2008-10-29 08:31:00"
And then wrap that transformed field in the TO_DATE mentioned earlier in your target SQL:
This is an effort to get a methodology that works 100% of the time when dealing with Oracle DATE fields. And it does.
"10/29/2008 8:31:00 AM" -> "2008-10-29 08:31:00"
And then wrap that transformed field in the TO_DATE mentioned earlier in your target SQL:
Code: Select all
TO_DATE(YourLink.YourField,"YYYY-MM-DD HH24:MI:SS")
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
![Rolling Eyes :roll:](./images/smilies/icon_rolleyes.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
sorry no more secrets this is it
my procedure expects a timestamp there are no conversions that take place in the procedure(no to date functions)
In my datastage job i have this function in the transformer
OCONV(ICONV(Field(DSLink2.CreateDate," ",1,1),"D"),"D-4YMD ") :" ": OCONV(Iconv(Field(DSLink2.CreateDate," ",2,2) , "MTS"),"MTS")
my output is this
14913 08:31:00
and my error
is not a valid month
So that function should be correct then what else do i need to do
I am sorry for the million questions but really appreciate your help
my procedure expects a timestamp there are no conversions that take place in the procedure(no to date functions)
In my datastage job i have this function in the transformer
OCONV(ICONV(Field(DSLink2.CreateDate," ",1,1),"D"),"D-4YMD ") :" ": OCONV(Iconv(Field(DSLink2.CreateDate," ",2,2) , "MTS"),"MTS")
my output is this
14913 08:31:00
and my error
is not a valid month
So that function should be correct then what else do i need to do
I am sorry for the million questions but really appreciate your help
What happened to the date part? Build a small test routine in the Manager and tweak the code until what comes out is what you want. All you need is one line of code, then tweak / save / compile / test until it works:aasaif wrote:my output is this
14913 08:31:00
Code: Select all
Ans = OCONV(ICONV(Field(Arg1," ",1,1),"D"),"D-YMD[4,2,2]") :" ": OCONV(Iconv(Field(Arg1," ",2,2) , "MTS"),"MTS")
Last edited by chulett on Fri Dec 26, 2008 10:07 am, edited 1 time in total.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
this is what i have now
DateTimeStampToOraOCIWithTime(OCONV(ICONV(Field(DSLink2.CreateDate," ",1,1),"D"),"D-DMY[2,A3,2] ") :" ": OCONV(Iconv(Field(DSLink2.CreateDate," ",2,2) , "MTS"),"MTS"))
I am getting invalid month so the format
must be i am assumming yyyy-mm-dd hh24:mm:ss
i am getting this and i dont know why
10/29/2008 8:31
does making a datafield timestamp automatically place it in the format in datastage?
DateTimeStampToOraOCIWithTime(OCONV(ICONV(Field(DSLink2.CreateDate," ",1,1),"D"),"D-DMY[2,A3,2] ") :" ": OCONV(Iconv(Field(DSLink2.CreateDate," ",2,2) , "MTS"),"MTS"))
I am getting invalid month so the format
must be i am assumming yyyy-mm-dd hh24:mm:ss
i am getting this and i dont know why
10/29/2008 8:31
does making a datafield timestamp automatically place it in the format in datastage?