convert MM/DD/YYYY IN TO DD-MON-YYYY

praburaj · Post by **praburaj** » Fri Dec 22, 2006 9:04 am

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

kris007 · Post by **kris007** » Fri Dec 22, 2006 9:14 am

praburaj wrote: Even i have tried to change the Target Column
Data type like Varchar, TimeStamp like this.

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.

chulett · Post by **chulett** » Fri Dec 22, 2006 9:27 am

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".

DSguru2B · Post by **DSguru2B** » Fri Dec 22, 2006 9:28 am

Is your target a Date field or Timestamp field ?
If date the do this

Code: Select all

OCONV(ICONV(in.date, "D/MDY[2,2,4]"), "D-YMD[4,2,2]")

This will convert your outpu to YYYY-MM-DD which oracle accepts.

chulett · Post by **chulett** » Fri Dec 22, 2006 9:30 am

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.

praburaj · Post by **praburaj** » Sat Dec 23, 2006 8:10 am

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

chulett · Post by **chulett** » Sat Dec 23, 2006 9:10 am

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, you don't "need" the date in that format.

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.

If you consistently treat your Oracle dates as Timestamps in DataStage and build yourself some routines to standardize date handling, then you will never have another issue handling Oracle dates again. It's really not that mysterious, folks.

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"

These kinds of OConv/IConv pairs are standard fare when handling any date, not just 'for Oracle' so suggest you get comfortable with them and make them part of your daily arsenal of weapons when attacking dates.

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.

praburaj · Post by **praburaj** » Mon Dec 25, 2006 1:45 am

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.

chulett · Post by **chulett** » Mon Dec 25, 2006 8:01 am

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.

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.

ray.wurlod · Post by **ray.wurlod** » Mon Dec 25, 2006 3:26 pm

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.

chulett · Post by **chulett** » Mon Dec 25, 2006 8:40 pm

I don't know about you, Ray, but I made the assumption that when someone complains about their end result being null that they know it shouldn't be. Call me crazy or a heretic, but... still, point taken and (hopefully) duly noted.

DSguru2B · Post by **DSguru2B** » Tue Dec 26, 2006 7:28 am

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.

praburaj · Post by **praburaj** » Tue Dec 26, 2006 7:34 am

Hai Chulett,

At last I got data for that column. But Still i have problem. I have applied ur code. But I did not get the correct result. Same Source data has inserted(22/12/2006) in to the target stage like this (22/12/2006).

My table Both Source and Target no primary key. How can i solve that?

DSguru2B · Post by **DSguru2B** » Tue Dec 26, 2006 7:36 am

So you got your date issue resolved. Is that what you are saying???

chulett · Post by **chulett** » Tue Dec 26, 2006 7:37 am

Add a primary key to both tables. [/jk]

What are you trying to solve now? Are you saying it is working? According to Sunshine you're still generating warnings...

DSXchange

convert MM/DD/YYYY IN TO DD-MON-YYYY

convert MM/DD/YYYY IN TO DD-MON-YYYY

Re: convert MM/DD/YYYY IN TO DD-MON-YYYY