Number to Date [message #231735] |
Wed, 18 April 2007 05:07  |
martinm
Messages: 2 Registered: April 2007
|
Junior Member |
|
|
Oracle 8.0.5.0.0 (Really!!)
Hi, I need to convert a date (number style) to dd/mm/yyyy format for the code below, and other than writing a nasty function, can't find a way to do it.
TRUNC(((T1.STARTDATE) - (to_date('1899-12-31', 'YYYY-MM-DD')))) - T5.UDF_NUM_FIELD1 * 1.4 as NotificationDate
Cheers,
Martin.
|
|
|
|
Re: Number to Date [message #231753 is a reply to message #231735] |
Wed, 18 April 2007 05:51   |
martinm
Messages: 2 Registered: April 2007
|
Junior Member |
|
|
I think I've actually sorted it (the 31/12/1899 equates to zero so can be removed), but
select trunc(sysdate - to_date('31 dec 1899')) from dual
returns 38824
whereas
select trunc(sysdate) from dual
returns 18/04/2007
So the to_date part is affecting the output. Still be nice to be able to convert 38824 to 18/04/2007 though!!
Cheers.
|
|
|
|
|
Re: Number to Date [message #232041 is a reply to message #231774] |
Thu, 19 April 2007 02:48  |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
My results disagree with the OP's too:SQL> SELECT sysdate,TRUNC(SYSDATE) - TO_DATE('31 dec 1899','DD mon YYYY') FROM dual;
SYSDATE TRUNC(SYSDATE)-TO_DATE('31DEC1899','DDMONYYYY')
--------- -----------------------------------------------
19-APR-07 39190
If the date system you're dealing with uses 31-dec 1899 as it's zero, then you can convert those into DATE columns like this:SELECT TO_DATE(2415020 + X,'J') FROM dual;
SQL> SELECT TO_DATE(2415020 + 39189,'J') FROM dual;
TO_DATE(2
---------
18-APR-07
|
|
|