Re: Date... Fixed Day, minus one month

From: Kay Kanekowski <kay.kanekowski_at_web.de>
Date: Fri, 23 Jul 2010 04:37:15 -0700 (PDT)
Message-ID: <092ef323-2744-4970-bceb-a45bace18b47_at_d8g2000yqf.googlegroups.com>

On 23 Jul., 12:49, Oliver <olit..._at_gmail.com> wrote:
> Lets say, we now have "23.07.2010 13:45:21" as dd.mm.yyyy hh:mm:ss.
> I like to get the following date 20.06.2010. This is "today minus one
> month" and "always day 20".

Hi Oliver,
try this:

select sysdate - d.offset_day day,

                TO_CHAR(ADD_MONTHS(sysdate - d.offset_day, -1), 'DD-MM-
YYYY HH:MM:SS') day_1_month,
       '20-' || TO_CHAR(ADD_MONTHS(sysdate - d.offset_day, -1), 'MM-

YYYY HH:MM:SS') only_20th
  from dual,

       (select level offset_day
          from dual
       connect by level <= 93) d;


DAY      DAY_1_MONTH         ONLY_20TH
-------- ------------------- -------------------
22.07.10 22-06-2010 01:06:14 20-06-2010 01:06:14
...
01.07.10 01-06-2010 01:06:14 20-06-2010 01:06:14
30.06.10 31-05-2010 01:05:14 20-05-2010 01:05:14
...

01.06.10 01-05-2010 01:05:14 20-05-2010 01:05:14 31.05.10 30-04-2010 01:04:14 20-04-2010 01:04:14

hth
Kay Received on Fri Jul 23 2010 - 06:37:15 CDT