Sorry!...its Date again...

[kalpna]

Hi!
I was trying to convert 20040202 format to 2004-Feb-02
I used
Oconv(Iconv('20040202', 'DYMD [4,2,2]'),'D-YMBD [4,3,2]')
But, it gives me
2004-FEB-02

Don't shout at me....
I have gone through 12 pages of search....
worked on it...cameup with this solution..but,...

Kalpna

[ArndW]

I'm not a DS machine right now, but I think that using a OCONV({YourDateString},'MCN') will do an initial capitals conversion for you - it's normally used for names and the like but I think it will ignore leading numerics.

[kcbland]

Are you saying you don't like FEB, but want Feb? Try adding OCONV(..., "MCT") for title case, but if that doesn't work you could always substring lowercase the right 2 digits of the month.

[kalpna]

I have Tried that already ..
It gives me 'feb'...I need Feb..

Yes!..I can do it with Downcase..
But, Plse!...give me a stright way..
Kalpna

[ArndW]

Kalpna,

did you try both "MCT" and "MCN"? You received 2 answers within 10 minutes of posting your original query and asking for another answer straight away? Since you know that the whole string is to be lowercase except for position 6 and you know that there is an UPCASE() and a DOWNCASE() function you could always experiment along those lines a bit.

[kalpna]

Sorry! Arnd
I tried both!...
yes!, I used DownCase...
But, Just like to know the other way...

anyways!..thanks
Kalpna

[kalpna]

Actually!,.the problem is with my Oracle table..
I tried different date formats .
It accepts only 02-Feb-2004 Format..(i confirmed it using sql).

I have acheived it using the above mentioned function and then using DownCase..
But, it says, converting string value '02-Feb-2004' to date is unsuccessful...

[ray.wurlod]

Missed this earlier. You needed

Code: Select all

Oconv(Iconv('20040202', 'DYMD'), 'D-YMBDL[4,A3,2]') 

The "L" in the Oconv second argument gives the alphabetic components in title case.

[chulett]

But, it says, converting string value '02-Feb-2004' to date is unsuccessful...

What makes you think Oracle wants it in that format? Best Practice would be to standardise on an ISO Standard Timestamp format to eliminate any date and/or timestamp handling issues with Oracle.

If you declare the datatype as Timestamp in an OCI stage, it will automatically use a TO_DATE() function when writing to Oracle and TO_CHAR() when reading. In both cases you'll either need or get a value in the following format:

Code: Select all

YYYY-MM-DD HH24:MI:SS

If you get your date into that format, and make it consistant through the use of common date handling routines everyone leverages, you will have zero problems with these fields in your jobs.

When working with DATE fields that have 'no time portion' (which is a misnomer, really - all dates have a time in Oracle) then simply append a zero time (:" 00:00:00") to the generated date value.

[kalpna]

Thanks.....Chulett!!..

Thanks Ray!!!!..


Cheers
Kalpna