Date... Fixed Day, minus one month
in [Oracle Help]
|
From: Oliver on 23 Jul 2010 06:49 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
From: Kay Kanekowski on 23 Jul 2010 07:37 On 23 Jul., 12:49, Oliver <olit...(a)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
From: Carlos on 23 Jul 2010 07:56 On Jul 23, 12:49 pm, Oliver <olit...(a)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 CARLOS(a)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.
From: Oliver on 23 Jul 2010 08:59 On 23 Jul., 13:37, Kay Kanekowski <kay.kanekow...(a)web.de> wrote: .... > '20-' || TO_CHAR(ADD_MONTHS(sysdate - d.offset_day, -1), 'MM- > YYYY HH:MM:SS') only_20th .... Does the Job. Thank you! VERY MUCH! Oliver
From: Oliver on 23 Jul 2010 09:06
On 23 Jul., 13:56, Carlos <miotromailcar...(a)netscape.net> wrote: > On Jul 23, 12:49 pm, Oliver <olit...(a)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...(a)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 |