Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    12
    Rep Power
    0

    Question Select all dates between two dates


    How can I select all of the dates between two dates?

    For example, given the start date 12/2/2003 and the end date 12/5/2003 I want to return:

    12/2/2003
    12/3/2003
    12/4/2003
    12/5/2003

    Is there a built in function for this? Is there a way for a function to return multiple rows?

    It has to be a function because I need to use it within other SQL statements.
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    60
    Rep Power
    11
    Assuming you have a "DATE" Oracle type column in a table. Let's presuem the column is named "foo". Then you want, in your WHERE cluase:

    TO_DATE('12/2/2003','MM/DD/YYYY') <= foo
    AND
    TO_DATE('12/5/2003','MM/DD/YYYY') >= foo

    you may obviously change the literal date strings which are the first args to be whate3ver you're searchign for.

    Note that I"m assumin you populated the 'foo' column with statements like:

    SET (foo) VALUES ( TO_DATE('12/2/2003','MM/DD/YYYY') )

    The TO_DATE() above in the WHERE will be midnight on that date... if your 'foo' col has time information (non-midnight date stamp) your where might get a little funny...

    -c
  4. #3
  5. No Profile Picture
    Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    305
    Rep Power
    11
    Functions can be return only one value in any programming language. Why dont you use the GROUP BY clause in SQL statement. Can you please send your query that you want to write?. An example of GROUP BY is look like that:

    SELECT hiredate from emp
    WHERE hiredate between '01-JAN-2003' AND '01-OCT-2003'
    GROUP BY hiredate
    /

    Regards,
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    12
    Rep Power
    0
    GROUP BY won't work for this project because it won't return any dates for which there are no entries in the table. Lets say we have a (simplified) table of orders like this:

    order_id INTEGER
    order_date DATE
    order_total NUMBER(10,2)

    If we have these entries:

    1 12/2/2003 12.30
    2 12/2/2003 45.60
    3 12/4/2003 78.90
    4 12/5/2003 10.23

    I need to select all the dates and how much was sold on each one, even 12/3 when nothing was sold:

    date num amount
    12/2/2003 2 57.90
    12/3/2003 0 00.00
    12/4/2003 1 78.90
    12/5/2003 1 10.23

    So, how do I fill in the extra dates with no entries? My initial thought was to somehow select each day between the two endpoints, outer join them to the orders, then group by to get the totals...

    It has to be SQL, I can't use a procedure in this context.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    68
    Rep Power
    11
    Hi augustd,

    You asked: So, how do I fill in the extra dates with no entries?

    One way to do it is via an inline-view, which will hold all the dates in the range.
    For example, the following query produces all the dates in the month of August (-2: two months ago).
    Then, you can use an outer join with this inline-view to ensure that each day is in the result set.


    select to_char(sysdate + 1 - rownum,'DD-Mon') as d
    from all_objects
    where trunc(sysdate + 1 - rownum,'MM') >= trunc(add_months(sysdate,-2),'MM')
    minus
    select to_char(sysdate + 1 - rownum,'DD-Mon') as d
    from all_objects
    where trunc(sysdate + 1 - rownum,'MM') > trunc(add_months(sysdate,-2),'MM')

    Cheers,
    Dan
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    12
    Rep Power
    0

    Arrow


    We have a winner! Thanks Dan.

    I modified it a little bit to get my arbitrary date range:

    SELECT TO_DATE('12/01/2003', 'MM/DD/YYYY') - 1 + rownum AS d
    FROM all_objects
    WHERE TO_DATE('12/01/2003', 'MM/DD/YYYY') - 1 + rownum <= TO_DATE('12/05/2003', 'MM/DD/YYYY')

    Returns:

    D
    2003-12-01 00:00:00
    2003-12-02 00:00:00
    2003-12-03 00:00:00
    2003-12-04 00:00:00
    2003-12-05 00:00:00
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2004
    Posts
    1
    Rep Power
    0

    Details abt rownum


    Hi can tell me what rownum implies in ur query???
  14. #8
  15. No Profile Picture
    Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    305
    Rep Power
    11
    In Oracle rownum is a pseudo column and its value not saved in the database, it shows the number of each record in the table, for example if you have five records in your table called 'department' and you run the query using rownum defined in your SELECT clause then your output would be look like that:


    SELECT rownum, deptno, dname
    FROM department
    /

    rownum deptno dname
    ------- ------- -------------------
    1 10 ACCOUNTS
    2 20 SALES
    3 30 MARKETING
    4 40 PURCHASE
    5 50 I.T
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    12
    Rep Power
    0
    Right, so date + rownum has the effect of adding one day to each date returned.

    So to return the next ten days:

    select rownum, trunc(sysdate + rownum) AS day
    from all_objects
    where rownum <= 10

    ROWNUM DAY
    1 2004-08-21 00:00:00
    2 2004-08-22 00:00:00
    3 2004-08-23 00:00:00
    4 2004-08-24 00:00:00
    5 2004-08-25 00:00:00
    6 2004-08-26 00:00:00
    7 2004-08-27 00:00:00
    8 2004-08-28 00:00:00
    9 2004-08-29 00:00:00
    10 2004-08-30 00:00:00
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2004
    Posts
    5
    Rep Power
    0

    problem while inserting values


    dear sir
    i have a button on a form.
    behind that button i have
    one select statement and othe insert into statement after one an other

    if i run only insert statement it works
    but
    if i run select statement first and after that an insert statement ,
    it gives error
    cannot insert.
    please help me for that problem

    raza
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    12
    Rep Power
    0
    Originally Posted by raza ul haq
    dear sir
    i have a button on a form. behind that button i have
    one select statement and othe insert into statement after one an other

    if i run only insert statement it works
    but if i run select statement first and after that an insert statement, it gives error cannot insert.
    please help me for that problem

    raza
    I'm not sure what you are asking. Are you getting an error message? What is your SQL? Does this have to do with dates?
  22. #12
  23. No Profile Picture
    Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    305
    Rep Power
    11
    By mistaken someone posted his question here, it is a separate thread, this question does not concern with date.
  24. #13
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Posts
    8
    Rep Power
    0
    FYI - Dan's solution will work great. Not that you should every run into this but just so your informed. You will only be able to return the number of records that exist in the table in your from clause. Also keep in mind that the table you use can effect performance.
  26. #14
  27. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2009
    Posts
    11
    Rep Power
    0

    How did you use this to group your records


    Originally Posted by augustd
    SELECT TO_DATE('12/01/2003', 'MM/DD/YYYY') - 1 + rownum AS d
    FROM all_objects
    WHERE TO_DATE('12/01/2003', 'MM/DD/YYYY') - 1 + rownum <= TO_DATE('12/05/2003', 'MM/DD/YYYY')
    hi
    how did you use this to group your records in your main query please
  28. #15
  29. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    12
    Rep Power
    0
    Originally Posted by patrickcoote
    hi
    how did you use this to group your records in your main query please
    You would make it into an inline view to select the dates and then left join those to the rest of your tables. Something like this:

    SELECT dates.d, SUM(orders.order_amount)
    FROM (
    SELECT TO_DATE('12/01/2003', 'MM/DD/YYYY') - 1 + rownum AS d
    FROM all_objects
    WHERE TO_DATE('12/01/2003', 'MM/DD/YYYY') - 1 + rownum <= TO_DATE('12/05/2003', 'MM/DD/YYYY')
    ) dates LEFT JOIN orders ON dates.d = orders.order_date
    GROUP BY dates.d;
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo