Date conversion from time stamp to yyyy/mm/dd

Dsnew · Post by **Dsnew** » Mon Oct 17, 2005 12:33 pm

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?

chulett · Post by **chulett** » Mon Oct 17, 2005 12:45 pm

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.

Dsnew · Post by **Dsnew** » Mon Oct 17, 2005 1:01 pm

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.

kcbland · Post by **kcbland** » Mon Oct 17, 2005 1:11 pm

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.

chulett · Post by **chulett** » Mon Oct 17, 2005 1:41 pm

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.

Dsnew · Post by **Dsnew** » Mon Oct 17, 2005 3:28 pm

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?

ketfos · Post by **ketfos** » Mon Oct 17, 2005 4:01 pm

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

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

Ketfos

Bala R · Post by **Bala R** » Mon Oct 17, 2005 6:57 pm

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.

kcbland · Post by **kcbland** » Mon Oct 17, 2005 7:49 pm

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.

gateleys · Post by **gateleys** » Tue Jan 24, 2006 9:26 am

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

Code: Select all

YYYY-MM-DD HH24:MI:SS.000

to get just the Date part

Code: Select all

YYYY-MM-DD

or if I just wanted to strip the column of the milliseconds

Code: Select all

YYYY-MM-DD HH24:MI:SS

.

gateleys · Post by **gateleys** » Tue Jan 24, 2006 2:29 pm

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.