Home » RDBMS Server » Server Administration » Translate a numérique to date
Translate a numérique to date [message #370833] Mon, 14 February 2000 12:47 Go to next message
Emmanuel
Messages: 6
Registered: February 2000
Junior Member
In database i have table with open_date = 950277645 (for example)
the software using oracle translate this number to a date format.

I try to make a select on open_date : OK
But i would like to have a date format like : "dd/mm/yy" ! please ?.......

Anyone know ?

Emmanuel from France-Paris
Re: Translate a numérique to date [message #370834 is a reply to message #370833] Mon, 14 February 2000 14:18 Go to previous messageGo to next message
Suresh
Messages: 189
Registered: December 1998
Senior Member
hi,
U can translate numeric number in the following way to dd/mm/yy format

ex:
value = 950201 -yy+mm+dd (assuming)

to_date(substr(to_char(950201),5,2)||'/'||substr(to_char(950201),3,2)||'/'||substr(to_char(950201),1,2),'dd/mm/yy')

output := 01-feb-95
Hope this helps
Suresh
Re: Translate a numérique to date [message #370837 is a reply to message #370833] Tue, 15 February 2000 01:14 Go to previous message
Atavur Rahaman S.A
Messages: 23
Registered: January 2000
Junior Member
Hello,

Good Day!

You can also do it in two differenct ways. The one with 'DECODE' and with 'TO_DATE' functions.

SQL> Select To_Date('&Any_Number','DD-MON-YY'/'DD-MM-YY') From Dual;

Output will be : 01-FEB-95 / 01-02-95

SQL> Select To_Date(substr(To_Char(any_Number),-2,2) || '-' || Decode(To_Number(Substr(To_char(Any_Number),-4,2)),1,'JAN',2,'FEB',3,'MAR',4,'APR',5,MAY',6,'JUN,7,'JUL',8,'AUG',9,'SEP',10,'OCT',11,'NOV',12,'DEC') || Substr(To_char(any_number),-6,2),'DD-MON-yy') From Dual;

Output wll be : 01-FEB-95

Hope ....it works.....

Regards
Atavur Rahaman S.a
Previous Topic: SQL
Next Topic: Problems with dbms_sql.parse (ora-6571)
Goto Forum:
  


Current Time: Fri Oct 30 02:28:11 CDT 2020