Home » SQL & PL/SQL » SQL & PL/SQL » Problem regarding date function
Problem regarding date function [message #265290] Thu, 06 September 2007 01:32 Go to next message
axiom
Messages: 26
Registered: August 2007
Junior Member
when i execute the following query the result is displayed
but i want the difference between sysdate & '01-APR-1996' also in date format.
if i use months_between function then it will give difference in number i want the generated number( 137.17686) in date format.

SQL> SELECT MONTHS_BETWEEN(SYSDATE,'01-APR-1996') FROM DUAL;

MONTHS_BETWEEN(SYSDATE,'01-APR-1996')
------------------------------------
                           137.17686

SQL> SELECT TO_CHAR(SYSDATE-TO_DATE('01-APR-1996','DD-MON-RRRR')) FROM DUAL;

TO_CHAR(SYSDATE-TO_DATE('01-APR-1996','D
----------------------------------------
4175.4925462962962962962962962962962963


thanku

[Edit: code must be put BETWEEN [code] and [/code]

[Updated on: Thu, 06 September 2007 01:45] by Moderator

Report message to a moderator

Re: Problem regarding date function [message #265291 is a reply to message #265290] Thu, 06 September 2007 01:37 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:
i want the difference between sysdate & '01-APR-1996' also in date format.


How ? Difference in date format? What you want?
Re: Problem regarding date function [message #265300 is a reply to message #265290] Thu, 06 September 2007 01:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
'01-APR-1996' is not a date, it is a string, use TO_DATE to convert it to a date.

Quote:
i want the generated number( 137.17686) in date format.

A difference between 2 dates is an interval not a date.

You can use:
SQL> select numtoyminterval(months_between(sysdate,to_date('01-APR-1996','DD-MON-YYYY')),
  2                         'MONTH') diff_year_month
  3  from dual
  4  /
DIFF_YEAR_MONTH
---------------------------------------------------------------------------
+000000011-05

1 row selected.

or
SQL> select numtodsinterval(sysdate-to_date('01-APR-1996','DD-MON-YYYY'),
  2                         'DAY') diff_day_to_second
  3  from dual
  4  /
DIFF_DAY_TO_SECOND
---------------------------------------------------------------------------
+000004175 08:52:28.000000000

1 row selected.

Regards
Michel


Re: Problem regarding date function [message #265306 is a reply to message #265291] Thu, 06 September 2007 02:00 Go to previous messageGo to next message
axiom
Messages: 26
Registered: August 2007
Junior Member
i got the solution for my problem
Re: Problem regarding date function [message #265309 is a reply to message #265290] Thu, 06 September 2007 02:02 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Can you please share how you got difference between two dates in date format?
Re: Problem regarding date function [message #265356 is a reply to message #265300] Thu, 06 September 2007 03:06 Go to previous messageGo to next message
axiom
Messages: 26
Registered: August 2007
Junior Member
SQL> select numtoyminterval(months_between(sysdate,to_date('01-APR-1996','DD-MON-YYYY')),
2 'MONTH') diff_year_month
3 from dual;
rom dual
*
RROR at line 3:
RA-03115: unsupported network datatype or representation
Re: Problem regarding date function [message #265364 is a reply to message #265356] Thu, 06 September 2007 03:20 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
axiom
i got the solution for my problem

a little bit later
ORA-03115: unsupported network datatype or representation


Whoa! What a great solution!
Re: Problem regarding date function [message #265368 is a reply to message #265290] Thu, 06 September 2007 03:37 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

I could not but laugh! (LOUDLY) Laughing Laughing Laughing
Re: Problem regarding date function [message #265499 is a reply to message #265290] Thu, 06 September 2007 07:48 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
And just to add to this "odd" thread, think about what you are asking.

Q: I want the difference between September 6th, 2007 and April 1st, 1996.

A: The answer is July 4th, 1948

Does that answer work for you?
Previous Topic: Cursor operation, checking return result
Next Topic: view
Goto Forum:
  


Current Time: Sun Dec 04 16:54:49 CST 2016

Total time taken to generate the page: 0.05829 seconds