12 Replies Latest reply: Mar 4, 2013 12:33 AM by Manik RSS

    Display all dates

    RajeshKanna
      Hi All,

      I want to display the all dates in a current calender. Startdate in a month is '01-MAR-2013'

      Can any one tell me?
        • 1. Re: Display all dates
          rp0428
          >
          I want to display the all dates in a current calender. Startdate in a month is '01-MAR-2013'
          >
          What does that even mean? Oracle has no 'display' capability.

          And there is no end to the calendar so there would be an infinite number to display.

          This query will select all of the dates for March
          select to_date('01-MAR-2013', 'dd-mon-yyyy') + level - 1 from dual
          connect by level < 32
          
          TO_DATE('01-MAR-2013','DD-MON-YYYY')+LEVEL-1
          3/1/2013
          3/2/2013
          . . .
          3/30/2013
          3/31/2013
          • 2. Re: Display all dates
            RajeshKanna
            Hello

            Current calender means current month only...
            • 3. Re: Display all dates
              Manik
              Try this.
              WITH t AS (SELECT TO_DATE ('01-MAR-2013', 'DD-MON-YYYY') dt FROM DUAL)
                  SELECT TRUNC (dt, 'YYYY') + ROWNUM - 1
                    FROM t
              CONNECT BY ROWNUM <=
                            (ADD_MONTHS (TRUNC (dt, 'YYYY'), 12) - TRUNC (dt, 'YYYY'));
              Cheers,
              Manik.
              • 4. Re: Display all dates
                rp0428
                >
                Current calender means current month only...
                >
                That is what the query I posted does.
                • 5. Re: Display all dates
                  Manik
                  RajeshKanna wrote:
                  Hello

                  Current calender means current month only...
                  WITH t AS (SELECT TO_DATE ('01-MAR-2013', 'DD-MON-YYYY') dt FROM DUAL)
                      SELECT TRUNC (dt, 'MM') + ROWNUM - 1
                        FROM t
                  CONNECT BY ROWNUM <= (ADD_MONTHS (TRUNC (dt, 'MM'), 1) - TRUNC (dt, 'MM'));
                  Cheers,
                  Manik.
                  • 6. Re: Display all dates
                    jeneesh
                    And if you want to display in style..
                    select lpad( Month, 20-(20-length(month))/2 ) month,
                               "Su", "Mo", "Tu", "We", "Th", "Fr", "Sa"
                          from (
                        select to_char(dt,'fmMonthfm YYYY') month,
                               to_char(dt+1,'iw') week,
                               max(decode(to_char(dt,'d'),'1',lpad(to_char(dt,'fmdd'),2))) "Su",
                               max(decode(to_char(dt,'d'),'2',lpad(to_char(dt,'fmdd'),2))) "Mo",
                               max(decode(to_char(dt,'d'),'3',lpad(to_char(dt,'fmdd'),2))) "Tu",
                               max(decode(to_char(dt,'d'),'4',lpad(to_char(dt,'fmdd'),2))) "We",
                              max(decode(to_char(dt,'d'),'5',lpad(to_char(dt,'fmdd'),2))) "Th",
                              max(decode(to_char(dt,'d'),'6',lpad(to_char(dt,'fmdd'),2))) "Fr",
                              max(decode(to_char(dt,'d'),'7',lpad(to_char(dt,'fmdd'),2))) "Sa"
                         from ( select trunc(sysdate,'mm')-1+rownum dt
                                  from all_objects
                                 where rownum <= extract( day from last_day(sysdate))
                       )
                        group by to_char(dt,'fmMonthfm YYYY'), to_char( dt+1, 'iw' )
                              )
                        order by to_date( month, 'Month YYYY' ), to_number(week);
                    
                    MONTH                Su Mo Tu We Th Fr Sa
                    -------------------- -- -- -- -- -- -- --
                         March 2013                      1  2 
                         March 2013       3  4  5  6  7  8  9 
                         March 2013      10 11 12 13 14 15 16 
                         March 2013      17 18 19 20 21 22 23 
                         March 2013      24 25 26 27 28 29 30 
                         March 2013      31                   
                    
                     6 rows selected 
                    Edited by: jeneesh on Mar 4, 2013 9:58 AM
                    The query is from AskTom
                    • 7. Re: Display all dates
                      ranit B
                      Another variant :
                      with xx as(
                          select TO_DATE('01-MAR-2013','dd-MON-yyyy') dt from dual 
                      )
                      select 
                           dt + level - 1
                      from xx
                          connect by level <= (ADD_MONTHS(dt,1) - dt);
                      • 8. Re: Display all dates
                        Rahul_India
                        RajeshKanna wrote:
                        Hi All,

                        I want to display the all dates in a current calender. Startdate in a month is '01-MAR-2013'
                        select trunc(sysdate,'MON') + level-1
                        
                        from dual
                        
                        connect by level <=31
                        just add 1.
                        edited in response to Manik. lol

                        Edited by: Rahul India on Mar 4, 2013 12:01 PM
                        • 9. Re: Display all dates
                          Manik
                          Rahul India wrote:
                          RajeshKanna wrote:
                          Hi All,

                          I want to display the all dates in a current calender. Startdate in a month is '01-MAR-2013'
                          select trunc(sysdate,'MON') + level-1
                          
                          from dual
                          
                          connect by level <=30
                          where is MAR-31 :)

                          Cheers,
                          Manik.
                          • 10. Re: Display all dates
                            Manik
                            >
                            just add 1.
                            edited in response to Manik. lol

                            Edited by: Rahul India on Mar 4, 2013 12:01 PM
                            The point is never HARDCODE.. :)
                            What if your query is run on FEB month ;)

                            Cheers,
                            Manik.
                            • 11. Re: Display all dates
                              jeneesh
                              Rahul India wrote:

                              just add 1.
                              edited in response to Manik. lol
                              What if, you are in Feb or Apr ?

                              You have to make it a varaiable
                              connect by level <= extract( day from last_day(sysdate));
                              Edited by: jeneesh on Mar 4, 2013 12:06 PM
                              • 12. Re: Display all dates
                                Rahul_India
                                >
                                I want to display the all dates in a current calender. Startdate in a month is '01-MAR-2013'
                                For any month this will will give you all the dates
                                select trunc(sysdate,'MON') + level-1
                                
                                from dual
                                
                                connect by level <=
                                last_day(sysdate)-(trunc(sysdate,'MON')) +1
                                Manik,Jeenesh:I was about to post the generalised code :)