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

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Fri, 23 Jul 2010 18:10:51 +0200
Message-ID: <4c49bf12$0$22920$e4fe514c_at_news.xs4all.nl>

Op 23-7-2010 15:06, Oliver schreef:
> On 23 Jul., 13:56, Carlos<miotromailcar..._at_netscape.net> wrote:
>> On Jul 23, 12:49 pm, Oliver<olit..._at_gmail.com> wrote:
>>
>>
>>
>>
>>
>>> 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
>>
>> CAR..._at_XE.localhost> select ADD_MONTHS(trunc(sysdate, 'MONTH'), -1 ) +
>> 19 from dual;
>>
>> ADD_MONTHS(TRUNC(SY
>> -------------------
>> 2010/06/20 00:00:00
>>
>> HTH.
>>
>> Cheers.
>>
>> Carlos.- Zitierten Text ausblenden -
>>
>
> 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
>

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 Received on Fri Jul 23 2010 - 11:10:51 CDT