Date Conversion formats

aasaif · Post by **aasaif** » Wed Dec 24, 2008 10:02 am

I know i am close thank you
this what i have
i modified the datatype to timestamp for the field
my function looks like this

OCONV(ICONV(Field(DSLink2.CreateDate," ",1,1),"D"),"D-DMY[2,A3,2] ") :" ": Field(DSLink2.CreateDate," ",2,2)

something is wrong anything you see off hand

chulett · Post by **chulett** » Wed Dec 24, 2008 10:09 am

I don't see anything wrong, what output do you get from the example input you posted?

aasaif · Post by **aasaif** » Wed Dec 24, 2008 10:13 am

error
literal does not match format string ORA-06512: at line 1

output
4913 8:31

chulett · Post by **chulett** » Wed Dec 24, 2008 10:19 am

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.

aasaif · Post by **aasaif** » Wed Dec 24, 2008 10:43 am

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

?

chulett · Post by **chulett** » Wed Dec 24, 2008 10:57 am

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:

Code: Select all

TO_DATE(YourLink.YourField,"YYYY-MM-DD HH24:MI:SS")

This is an effort to get a methodology that works 100% of the time when dealing with Oracle DATE fields. And it does.

aasaif · Post by **aasaif** » Wed Dec 24, 2008 11:05 am

Can i still leave the datatype of the procedures as type Date

chulett · Post by **chulett** » Wed Dec 24, 2008 11:10 am

Procedures?

aasaif · Post by **aasaif** » Wed Dec 24, 2008 11:12 am

yeah i am passing the data into a stored procedure stage

aasaif · Post by **aasaif** » Wed Dec 24, 2008 11:20 am

do you think i can use the DataGenerictoOraclewith time function
anything to now have to declare the date field in the procedure to varchar

chulett · Post by **chulett** » Wed Dec 24, 2008 11:21 am

More secrets. Ok, so a stored procedure is the target for this, not an OCI or ODBC stage? Your answer depends entirely on what the stored procedure is expecting... got any clue on that? Meaning, is it expecting a native DATE or a string that it reformats or ??? If it is looking for a DATE then what we've discussed should work fine with no changes anywhere.

aasaif · Post by **aasaif** » Wed Dec 24, 2008 12:04 pm

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

chulett · Post by **chulett** » Wed Dec 24, 2008 2:34 pm

aasaif wrote:my output is this
14913 08:31:00

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:

Code: Select all

Ans = OCONV(ICONV(Field(Arg1," ",1,1),"D"),"D-YMD[4,2,2]") :" ": OCONV(Iconv(Field(Arg1," ",2,2) , "MTS"),"MTS")

aasaif · Post by **aasaif** » Fri Dec 26, 2008 8:54 am

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?

chulett · Post by **chulett** » Fri Dec 26, 2008 9:23 am

Don't assume. Build a test routine as suggested and make it work properly.

does making a datafield timestamp automatically place it in the format in datastage?

No, that's your job.