Date Conversion formats

aasaif · Post by **aasaif** » Fri Dec 26, 2008 9:55 am

I tried everything changing date i built a test routine no matter what format i specify in the oconv function
it always displays at mm/dd/yyyy format

I appreciate all your help. I am going to see if i can an support account setup for help with this.

chulett · Post by **chulett** » Fri Dec 26, 2008 10:12 am

My help doesn't seem to be helping all that much.

Post some samples again of what your input data looks like, the 'timestamp' information you are trying to convert, maybe I'm missing something there. The code I posted earlier should work fine for the date portion, I'm unclear why it isn't working for you. The time portion may still need some messing with, however. Again, post some examples... real examples.

aasaif · Post by **aasaif** » Fri Dec 26, 2008 10:41 am

the data in the source file "10/29/2008 8:31"

this is what i did in datastage i change the transformer output type to be of data and this is the derivation i had in the transformer
OCONV(ICONV(Field(DSLink2.CreateDate," ",1,1),"D"),"D-DMY[2,A3,2] ")

loaded the row in the database
and the data looked like this in the database

29-OCT-20 08.00.00.000000 AM

when i change it to datatype timestamp
i get the invalid month oracle error

chulett · Post by **chulett** » Fri Dec 26, 2008 10:46 am

So... this is a TIMESTAMP field in Oracle, not a DATE? One last secret?

And post several examples, include morning and afternoon times. I thought your first examples had AM/PM in them...

aasaif · Post by **aasaif** » Fri Dec 26, 2008 11:04 am

the data in the source file "10/29/2008 8:31"

this is what i did in datastage i change the transformer output type to be of data and this is the derivation i had in the transformer
OCONV(ICONV(Field(DSLink2.CreateDate," ",1,1),"D"),"D-DMY[2,A3,2] ")

loaded the row in the database
and the data looked like this in the database

29-OCT-20 08.00.00.000000 AM

when i change it to datatype timestamp
i get the invalid month oracle error

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

It looks like to me when you specify a timestamp in datastage it overrides and converts into another format
and oracle does not like. Oracle is looking for yyyy-mm-dd hh:mm:ss

however when i change the derivation
OCONV(ICONV(Field(DSLink2.CreateDate," ",1,1),"D"),"D4YMD ") :" ": OCONV(Iconv(Field(DSLink2.CreateDate," ",2,2) , "MTS"),"MTS")
to specify it I still get an error

sorry the data in the source file is 10/29/2008 8:31:00 AM

The field in oracle is a timestamp

here are some rows in the database after the etl ran
29-OCT-20 08.00.00.000000 AM
03-NOV-20 08.00.00.000000 AM
02-NOV-20 08.00.00.000000 AM
06-NOV-20 08.00.00.000000 AM
06-NOV-20 08.00.00.000000 AM
06-NOV-20 08.00.00.000000 AM
06-NOV-20 08.00.00.000000 AM
06-NOV-20 08.00.00.000000 AM
06-NOV-20 08.00.00.000000 AM
06-NOV-20 08.00.00.000000 AM

chulett · Post by **chulett** » Fri Dec 26, 2008 11:57 am

A couple of points. If you read the docs, you'll find that TIMESTAMP types are not supported, so you need to play some games. Use a Varchar field in the job and a TO_TIMESTAMP() function in the sql rather than a TO_DATE(). Remind me again, what is your target stage? I don't think you've ever specifically said.

Secondly, Oracle isn't expecting anything in particular other that well-formed data... well, unless you are trying to match the current NLS format which really isn't a best practice. It is, however, expecting you to reliably tell it what your source data looks like (using the TO_DATE() and TO_TIMESTAMP() conversion functions) and will bust you when you get it wrong.

ps. Want multiple samples of the input data, not the output, so I can see if there's any variation in what you are attempting to convert.

aasaif · Post by **aasaif** » Fri Dec 26, 2008 2:10 pm

I gave up I put a request to the DBA to change the procedure to have the procedure parameters as VARCHAR and i sent them the to_timestamp sql to modify that field.

So now I will just pass it as a varchar throughout the datastage job

THanks for your help.
Datastage really has to do something to be able to pass in dates alot easier in oracle

JRodriguez · Post by **JRodriguez** » Fri Dec 26, 2008 2:58 pm

aasaif:

While converting the date value from internal to external using Oconv function, the mask in the Oconv function is specifying to spell out the month piece - A3 - but you are expecting digits

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

A3 mean spell out the month piece: JAN, FEB ...DEC

Use this one instead

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

After that just play around with the time piece as Chulet suggested

Julio

ray.wurlod · Post by **ray.wurlod** » Fri Dec 26, 2008 3:52 pm

DataStage has no problem at all preparing data for Oracle.

People who write incomplete or incorrect specifications are the source of the "problem".

Code: Select all

Oconv(TheDate,"D-YMD[4,2,2]") : " " : Oconv(TheTime("MTS:")

will generate what you asked for, given internal format date and time.

chulett · Post by **chulett** » Fri Dec 26, 2008 5:18 pm

aasaif wrote:I gave up I put a request to the DBA to change the procedure to have the procedure parameters as VARCHAR and i sent them the to_timestamp sql to modify that field.

So now I will just pass it as a varchar throughout the datastage job

THanks for your help.
Datastage really has to do something to be able to pass in dates alot easier in oracle

Hmmm... as noted, there really shouldn't be a problem accomplishing what you need but it is a learning experience. I literally have several thousand jobs crafted here and there and the vast majority of them have a date or timestamp field in them. They work just fine.

You've just moved the problem over for Oracle to handle but you'll still need to match a mask with the incoming data, same as in DataStage.

chulett · Post by **chulett** » Sun Dec 28, 2008 10:57 am

FYI, just for completeness and to combine what Ray and I posted:

Code: Select all

OConv(IConv(Field(YourField," ",1,1),"D"),"D-YMD[4,2,2]") : " " : OConv(Iconv(Convert(" ","",Field(YourField," ",2,2)),"MTS"),"MTS:")

The only thing 'unusual' here is the need to take the space out of the time as DataStage is expecting "11:30PM" rather than "11:30 PM".

aasaif · Post by **aasaif** » Mon Dec 29, 2008 11:26 am

I have tried all the conversion functions but point is
Datatype of Date in datastage does not allow for time
so when you change it to timestamp it does now all you to put it in my nls format of dd-mon-yyyy. I tried all the conversions modifying datatypes back and fourth and i can not get it to work. I am not hiding anything

i have a source file that has
this format 10/29/2008 08:31:00

and i need to pass in a stored procedure that has that field of datatype date in oracle

but the nls format on the database has this dd-MON-yyyy

aasaif · Post by **aasaif** » Mon Dec 29, 2008 11:32 am

i mean datatype timestamp in the stored procedure

chulett · Post by **chulett** » Mon Dec 29, 2008 11:37 am

If you really need to match that NLS format, then set the field to a Varchar in the job and change the "D-YMD[4,2,2]" part to "D-DMY[2,A3,4]" as previously posted.