OraFAQ Forum: SQL & PL/SQL » Number to Date

Home » SQL & PL/SQL » SQL & PL/SQL » Number to Date

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

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.

Report message to a moderator

Re: Number to Date [message #231742 is a reply to message #231735]

Wed, 18 April 2007 05:23

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Perhaps you'd like to share some details, like the field layout or how the calculation should be.

MHE

Report message to a moderator

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.

Report message to a moderator

Re: Number to Date [message #231760 is a reply to message #231753]

Wed, 18 April 2007 06:07

Maaher
Messages: 7065
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

Report message to a moderator

Re: Number to Date [message #231774 is a reply to message #231753]

Wed, 18 April 2007 06:51

Michel Cadot
Messages: 68763
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

Report message to a moderator

Previous Topic:	Function help
Next Topic:	connecting problem

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jul 29 23:29:37 CDT 2025