Date conversion again...

[Anand K]

Hi All

I have a date coming in from a sequential file as "20031010" (YYYYMMDD) which I need to convert into 2003/10/10 (YYYY/MM/DD) and store in a Oracle table

Please help me how to get this.


Thnx in advance

Anand K

[essaion]

Please make a search before posting an issue that can easily be found within this forum : in the upper navigation frame, click "search", the fill the "search for" field with your keywords (could be "date conversion", for an example), make sure the radio button "match all keywords" is ticked, then run the search.

"Help yourself so God can help you"...

Tip : you can also look at the designer help or the documentation provided together with datastage...

[chulett]

I have a date coming in from a sequential file as "20031010" (YYYYMMDD) which I need to convert into 2003/10/10 (YYYY/MM/DD) and store in a Oracle table

First off, welcome to the Forums!

As you've guessed, this issue has been covered before and lots of information can be found by searching. Looking for "iconv", "oconv" and "status" will net you what you need to do the job right. Your other option would be to substring it apart and paste it back together again the way you want it, but then you'd never know if it's good or not until Oracle spits it back at you.

If you are using the OCI stage for your Oracle work, you typically want to get it into YYYY-MM-DD format. Depends on the data type you choose, I generally stick with Timestamp and make sure there is a time portion, even if it is all zeroes. If you haven't already, read the .pdf docs for the OCI plugin, it will explain how it works and what it is expecting.

[Teej]

Actually, if the Oracle output is a Varchar, a string chopping routine can be used.

You know blah[1,4] : "/" : blah[5,2] : "/" : blah[7,2]

-T.J.

[auvray.muriel]

hello,

you can use the routine "DateTimeStamptoOraOCI" which is in the following tree structure:
Routines/sdk/Date

Code: Select all

RoutineName = 'DateTimeStamptoOraOCI'

    If Arg1 Then
   	Ans = Arg1[1,4]:"/":Arg1[5,2]:"/":Arg1[7,2]:" 00:00:00"
    End Else
   	Ans = Arg1
    End

Bye

Muriel