OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Date formats

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Date formats [message #2280]

Wed, 03 July 2002 09:34

spat
Messages: 10
Registered: March 2002

Junior Member

I have created a cursor to update tables. And update a field using

'R' || SUBSTR(to_char(my_date, 'MM/DD'),1,5)

This would give dates such as

R10/23
R02/15
R03/08

What I really need is for the dates to be formated without the padding, so that the final results would show as

R10/23
R2/15
R3/8

Any help that is provided is extremely appreciated. Thank you.

Report message to a moderator

Re: Date formats [message #2283 is a reply to message #2280]

Wed, 03 July 2002 10:24

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

split the date(MM and DD)and use ltrim to remove the zeros and concat again.
something like this.

SQL> ed
Wrote file afiedt.buf

  1*  select 'R' ||ltrim(SUBSTR(to_char(hiredate, 'MM'),1,5),0)||'/'||ltrim(SUBSTR(to_char(hiredate, 'dd'),1,5),0) from emp
SQL> /

'R'||L
------
R12/17
R2/20
R2/22
R4/2
R9/28
R5/1
R6/9
R4/19

Report message to a moderator

Re: Date formats [message #2293 is a reply to message #2280]

Wed, 03 July 2002 14:14

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

just curious!
why do you need a substr there?
SQL> ed
Wrote file afiedt.buf

1* select 'R' ||ltrim(to_char(hiredate, 'MM'),0)||'/'||ltrim(to_char(hiredate, 'dd'),0) from emp
SQL> /

'R'||L
------
R12/17
R2/20
R2/22
R4/2
R9/28
R5/1
R6/9
R4/19

Report message to a moderator

Previous Topic:	Julian Date??
Next Topic:	How do I query a dynamic table type plsql

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 23:10:10 CDT 2024