DSXchange

I know this topic has been covered many times, but could not get the exact answer
I want to convert date from 2005-04-15 00:00:00 to 2005-04-15 or 2005/04/15.
My destination table in oracle is Date. In datastage it is a Timestamp
Can anybody help?

What you posted is easy as it's exactly what it needs to look like for an Oracle DATE field declared as a Timestamp in DataStage:
So for that just send it on through - no conversion needed.

For something coming in like '2005/04/15', it's just a matter of rearranging the bits using Iconv and Oconv and then sticking the zeroes on the end:
Something like that should work.

Craig,

In need the reverse. From timestamp to yyyy/mm/dd.

To be more specific, my requirement is to put a date in yyyy/mm/dd format, rather than in YYYY-MM-DD HH24:MI:SS format.
It could be the current date or a future date.

The OCI stage automatically puts a TO_DATE() on DATE datatype columns and your dates HAVE TO BE IN THE STRING FORM OF YYYY-MM-DD or YYYY-MM-DD HH24:MI:SS. So, why do you want to make things not work right and prolong this post?

If you want your answer, even if it's not what you need to do, just use string parsing to juggle the fields around or use OCONV(ICONV(yourdate, "yourdateformat"), "newdateformat") type logic. Look in your DS BASIC manual or search this forum. I'm sure from Craigs post here you can figure out what it should be.

Actually, you don't need to change it around.

If your destination target is a DATE field in Oracle, then simply declare it as a Timestamp datatype. I'll say it again - No conversion required, simply send it to the OCI stage as is.

Thanks guys,

My be i did not ask the question right
I dont have a date field in my source file, i am just creating an extra field in the table using the transformer.
I need to put in today's date or any given date in the format YYYY-MM-DD or YYYY/MM/DD
I was using DSJOBSTARTDATE for this purpose, and getting the output in the oracle table in the format YYYY-MM-DD HH24:MI:SS.
May be i am doing something wrong !!!
How do i get a date in the format YYYY/MM/DD in to an oracle table?

Hi,
Use internal date function of datastage with Oconv() s follows

Oconv(Date() , "D/YMD[4,Z,Z]")

Ketfos

More precisely, Oconv(DATE(),"D/YMD[4,2,2]"):" ":Oconv(TIME(),"MTS"). If you don't need the exact time part just append " 00:00:00" to the date part, as in Craig's solution.

Okay, one last time. How are you viewing your result in the Oracle table? Are you browsing with DS's view data button?

You do realize that Oracle stores dates in an internal integer value and that it is up to the device querying the data to format it in a manner you like? Therefore, DS puts a TO_CHAR(xxx, 'YYYY-MM-DD HH24:MI:SS') on all DATE fields in the SQL when it queries the database. If you're using Toad or Embarcadero to browse the data, you can configure those tools to display the date however you like.

It seems that you are loading the data successfully, but when looking at the data you're not seeing slash separated data like you thought you loaded.

chulett wrote:What you posted is easy as it's exactly what it needs to look like for an Oracle DATE field declared as a Timestamp in DataStage:

Code: Select all

YYYY-MM-DD HH24:MI:SS

So for that just send it on through - no conversion needed.

For something coming in like '2005/04/15', it's just a matter of rearranging the bits using Iconv and Oconv and then sticking the zeroes on the end:

Code: Select all

OConv(Iconv(YourField,"D/YMD"),"D-YMD[4,2,2]") : " 00:00:00"

Something like that should work.

What if I wanted to convert from a Timestamp type with milliseconds to get just the Date part or if I just wanted to strip the column of the milliseconds.

Since DS performs a TO_DATE() operation on the value for the date that is supplied, I performed Left(MyDate,19) to eliminate the .SSS from the time picture. It works.