Oracle functions

Replies

• 

  123_neha

  i need to list down all the dates between two dates using oracle
  both dates belong to different columns
• 

  Prashant Munshi

  The following select statement would give you the dates between the two dates with an explanation also follows the solution -
  
  select to_date('', 'dd-mon-yyyy') + rownum - 1 as dt
  from all_objects
  where to_date('','dd-mon-yyyy') + rownum - 1 <= to_date('', 'dd-mon-yyyy');
  
  Here rownum is pseudo column sequential number given to each row returned by the query. There is no specialty of using all_objects table except that it is table with huge number of rows and as select statement would require enough number of rows to display the result as internally the "select" keyword of SQL is actually a loop and all_objects table would provide enough rows for its iteration until the last in the range is displayed. The limitation of the query is however is that it can not give range of dates beyond the number of rows contained in the table referenced.
  
  I hope you would be satisfied with the answer and the explanation.
  
  Here the assumption is that you would require the limits of range (start/end date) to be also included in the result.