Re: Dates

From: Jonathan Gennick <jonathan_at_gennick.com>
Date: 2000/05/25
Message-ID: <g4kqis4dsfnte8rbuhbe2pr8kcun45gqo8@4ax.com>#1/1

I did something like this once when I needed to get years. Create a table with 31 rows, with the rows having the values from 1-31.

SQL> SELECT * FROM MON
  2 /

   MON_DAY

---

         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        11

   MON_DAY

---

        12
        13
        14
        15
        16
        17
        18
        19
        20
        21
        22

   MON_DAY

---

        23
        24
        25
        26
        27
        28
        29
        30
        31

31 rows selected.

Then us a query like the one shown here:

SQL> SELECT mon_day, TO_CHAR(SYSDATE,'Mon') ||

  2                  '-' || LTRIM(TO_CHAR(mon_day))
  3                  || '-' || TO_CHAR(SYSDATE,'YYYY')

  4 FROM mon
  5 WHERE mon_day
  6 <= TO_NUMBER(TO_CHAR(LAST_DAY(SYSDATE),'DD'));

   MON_DAY TO_CHAR(SYSDATE,'MON')||'-'||LTRIM(TO_CHAR(MON_DA

---------- -------------------------------------------------
         1 May-1-2000
         2 May-2-2000
         3 May-3-2000
         4 May-4-2000
         5 May-5-2000
         6 May-6-2000
         7 May-7-2000
         8 May-8-2000
         9 May-9-2000
        10 May-10-2000
        11 May-11-2000

   MON_DAY TO_CHAR(SYSDATE,'MON')||'-'||LTRIM(TO_CHAR(MON_DA

---------- -------------------------------------------------
        12 May-12-2000
        13 May-13-2000
        14 May-14-2000
        15 May-15-2000
        16 May-16-2000
        17 May-17-2000
        18 May-18-2000
        19 May-19-2000
        20 May-20-2000
        21 May-21-2000
        22 May-22-2000

   MON_DAY TO_CHAR(SYSDATE,'MON')||'-'||LTRIM(TO_CHAR(MON_DA

---------- -------------------------------------------------
        23 May-23-2000
        24 May-24-2000
        25 May-25-2000
        26 May-26-2000
        27 May-27-2000
        28 May-28-2000
        29 May-29-2000
        30 May-30-2000
        31 May-31-2000

31 rows selected.

There may be some ways to avoid so many conversions. I'd have to think about that for awhile.

Hope this helps,

Jonathan

On Wed, 24 May 2000 15:01:42 -0500, Eleanor Sokol <eleanor_sokol_at_bmc.com> wrote:

>I'm trying to create a SQL statement which will give me all the days of
>a given month as individual rows. That is, the first row would be for
>01May00, the 2nd row would have 02May00, through 31May00.
Received on Thu May 25 2000 - 00:00:00 CDT