Date... Fixed Day, minus one month
in [Oracle Help]
|
From: Shakespeare on 23 Jul 2010 12:10 Op 23-7-2010 15:06, Oliver schreef: > 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 > 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
From: Oliver on 6 Aug 2010 04:31
On 23 Jul., 18:10, Shakespeare <what...(a)xs4all.nl> wrote: .... > > 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 Agree and changed my code. Thank you! Oliver |