Re: Date... Fixed Day, minus one month
From: Oliver <olitogo_at_gmail.com>
Date: Fri, 23 Jul 2010 06:06:23 -0700 (PDT)
Message-ID: <ad6d9e2e-4de7-4fda-85b0-d360540586a6_at_w31g2000yqb.googlegroups.com>
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 -
>
Date: Fri, 23 Jul 2010 06:06:23 -0700 (PDT)
Message-ID: <ad6d9e2e-4de7-4fda-85b0-d360540586a6_at_w31g2000yqb.googlegroups.com>
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 Received on Fri Jul 23 2010 - 08:06:23 CDT