Sorry!...its Date again...
Moderators: chulett, rschirm, roy
Sorry!...its Date again...
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
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
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
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.
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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...
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...
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Missed this earlier. You needed
The "L" in the Oconv second argument gives the alphabetic components in title case.
Code: Select all
Oconv(Iconv('20040202', 'DYMD'), 'D-YMBDL[4,A3,2]')
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.kalpna wrote:But, it says, converting string value '02-Feb-2004' to date is unsuccessful...
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
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers