Date conversion

[mohanraj]

hi to all

My source data is from flat files and in that one column contains DATE in these fromats
20051121 and 2005521
and i want to put in oracle OCI in its default format
i tried it by doing Oconv and Iconv.But i could able to convert only one type that is 20051121.I can convert other also in the same way but both the types are in same column only.

So my question is if the incoming data in a column contains 3-4 formats of DATE type how can i convert it to one single format.

Whether i have to apply one condition for each type and OR between them.

How can i do it

Thanks in advance

raju

[chulett]

What "3-4 formats of DATE" do you speak? You've only shown one.

If you really need to handle that, a custom routine is in order. One that attempts to detect the incoming format and handles it accordingly, whatever that may mean.

Please post what you've tried so far.

[kcbland]

How could you distinguish 2005111 from 2005111? Is it 2005-01-11 or 2005-11-01?

[DSguru2B]

As Craig requested, please provide all possible formats that you can get. This way it will be easier to us to point you in the right direction, even a possible solution.

[chulett]

Yerk... hope nobody is sending dates as ambiuous as that. I've seen ones where the month may not include a leading zero but the day would always, so the answer in that case would be 2005-01-11.

But it does complicate things... for no good reason...

[roy]

Hi,
There are several options.
Recap:
You have a file with a month that doesn't have leading zero in single digit months; i.e. 20051121 and 2005521

So you need to find a way that works for both types, or conditionally convert the apropriate type after you verify which one you have

one way might be using a routine to inshure a fixed format such as this:

Code: Select all

year = Arg1[1,4]
monthLen = Len(Arg1) - 6
month = Arg1[5,monthLen]
If monthLen = 1 Then month = "0" : month
days = Arg1[5 + monthLen,2]
Ans = year : month : days

You can modify it to include any conversions you might need as well.

If you have also varying days with no leading zeros you might be in a bit of a problem, since you have no way to differ first of November 2005111 from January 11th 2005111 and similar dates.


IHTH,

[mohanraj]

hi roy

My problem is in one of my column one format of date is yyyymmdd
and one more format is without leading month that is like yyyymdd.

Then how can i convert it into dd-mon-yyyy format

[ray.wurlod]

Iconv() and Oconv(). But first you need a guaranteed mechanism for standardising your date format, adding a leading zero to the month where necessary. For example

Code: Select all

If Len(TheDate) = 7 Then Left(TheDate,4) : "0" : Right(TheDate,3) Else TheDate

Apply an Iconv() function to this, specifying that the date components are in year, month, day order.

Code: Select all

Iconv(result1, "DYMD")

Apply an Oconv() to that result, to generate the date in your required format; in your case with "-" as the delimiter character, a two digit day, a three-character alphabetic abbreviated month name and a four digit year.

Code: Select all

Oconv(result2, "D-DMBY[2,A3,4]")

Putting it all together you get

Code: Select all

Oconv(Iconv(If Len(InLink.TheDate) = 7 Then Left(InLink.TheDate,4) : "0" : Right(InLink.TheDate, 3), "DYMD"), "D-DMBY[2,A3,4]")

[jvr_3jv]

Hi Raju,

Try with this following code and the Inlink.Date is the in column name
data type is varchar

if len(Inlink.Date)=8 then Inlink.Date[7,2]:"-":Inlink.Date[5,2]:"-":Inlink.Date[1,4] else Inlink.Date[6,2]:"-":"0":Inlink.Date[5,1]:"-":Inlink.Date[1,4]

before passing to the outlink u need to use the following function which will convert the varchar format to Date format

StringToDate(StageVar,"%dd-%mm-%yyyy")

Please let me know any confusion on this.
Regards,
Venkat :D

[ray.wurlod]

Venkat's solution will not work in a server job because the StringToDate() function is only available in parallel jobs.

[newtier]

Venkat's solution will not work in a server job because the StringToDate() function is only available in parallel jobs. ...

Or omit the function call, and in the ORAOCI stage make sure the generated SQL (or you add in user-defined SQL) has the TO_DATE() function for that column.