Home » SQL & PL/SQL » SQL & PL/SQL » Number to Date
Number to Date [message #231735] Wed, 18 April 2007 05:07 Go to next message
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 #231742 is a reply to message #231735] Wed, 18 April 2007 05:23 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Perhaps you'd like to share some details, like the field layout or how the calculation should be.

MHE
Re: Number to Date [message #231753 is a reply to message #231735] Wed, 18 April 2007 05:51 Go to previous messageGo to next message
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 #231760 is a reply to message #231753] Wed, 18 April 2007 06:07 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Make sure you add a date format to your TO_DATE. If you don't Oracle will take your default NLS_DATE_FORMAT and that can be changed at system and session level.

MHE
Re: Number to Date [message #231774 is a reply to message #231753] Wed, 18 April 2007 06:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What about:
SQL> select to_date('31 dec 1899','DD mon YYYY') + 38824 from dual;
TO_DATE('3
----------
18/04/2006

1 row selected.

Very weird when I used your query I get:
SQL> select  trunc(sysdate) - to_date('31 dec 1899','DD mon YYYY') from dual;
TRUNC(SYSDATE)-TO_DATE('31DEC1899','DDMONYYYY')
-----------------------------------------------
                                          39189

1 row selected.

Seems you are one year off.

Regards
Michel
Re: Number to Date [message #232041 is a reply to message #231774] Thu, 19 April 2007 02:48 Go to previous message
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
Previous Topic: Function help
Next Topic: connecting problem
Goto Forum:
  


Current Time: Thu Dec 08 22:25:32 CST 2016

Total time taken to generate the page: 0.06005 seconds