Dear all

It might be a trivial issue for you.. but it keeps me busy and I'd be
glad for help.


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".

What I have currently is:
TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'DD-MM-YYYY HH:MM:SS')

I've been traing with a lot of things but couldn't get it working...

How can I have a fixed day in DD?

Oliver

On 23 Jul., 12:49, Oliver <olit... (AT) gmail (DOT) com> wrote:
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

On Jul 23, 12:49*pm, Oliver <olit... (AT) gmail (DOT) com> wrote:
CARLOS (AT) XE (DOT) localhost> select ADD_MONTHS(trunc(sysdate, 'MONTH'), -1 ) +
19 from dual;

ADD_MONTHS(TRUNC(SY
-------------------
2010/06/20 00:00:00

HTH.

Cheers.

Carlos.

On 23 Jul., 13:37, Kay Kanekowski <kay.kanekow... (AT) web (DOT) de> wrote:
....

Does the Job. Thank you! VERY MUCH!

Oliver

On 23 Jul., 13:56, Carlos <miotromailcar... (AT) netscape (DOT) net> wrote:
Hi Carlos

Thank you. I've decided to use Kays solution.
It feels more "right" as it does what I've asked.

Altough your solution it does a count in a mathematical term and not
like a string concatination.
....so, it is more an conceptual/ theoretical issue.

What I'd prefere realy - from conceptual point of view - would be a
syntax like '20-mm-yyyy' or 'dd=20.mm.yyyy'. According this logic we
could have 'Last_Day(dd)-mm=08-yyyy=2010' ...but that sure is not
working.
Maybe I could ask oracle to do me a favor (I think they owe me
one...) ;-)

Oliver

Op 23-7-2010 15:06, Oliver schreef:
How about:

SQL> select trunc(sysdate - interval '1' month,'MM') + interval '19' day
from dual;
I like the interval syntax because it documents exactly what and how
many you are adding to a date.

Shakespeare

On 23 Jul., 18:10, Shakespeare <what... (AT) xs4all (DOT) nl> wrote:
....
Agree and changed my code.
Thank you!

Oliver