CrazyEngineers
  • Oracle functions

    123_neha

    Member

    Updated: Oct 17, 2024
    Views: 913
    Hi guys!!
    Its time to worm up with your knowledge about oracle functions...
    0
    Replies
Howdy guest!
Dear guest, you must be logged-in to participate on CrazyEngineers. We would love to have you as a member of our community. Consider creating an account or login.
Replies
  • 123_neha

    MemberMar 3, 2013

    i need to list down all the dates between two dates using oracle
    both dates belong to different columns
    Are you sure? This action cannot be undone.
    Cancel
  • Prashant Munshi

    MemberMar 5, 2013

    The following select statement would give you the dates between the two dates with an explanation also follows the solution -

    select to_date('<given start date>', 'dd-mon-yyyy') + rownum - 1 as dt
    from all_objects
    where to_date('<given start date>','dd-mon-yyyy') + rownum - 1 <= to_date('<given end 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.
    Are you sure? This action cannot be undone.
    Cancel
Home Channels Search Login Register