Monster dating service missing database vba
Also and therefore, it reads more clearly, if you were to translate it to a natural language (i.e if you needed to explain it to someone or someone were trying to understand what it's doing by reading it).
BTW, I say it's a "bug" with the "design" of the Add Months Function, not just the Function because it's doing what its docs claim it was designed to do.
Therefore, it could also apply to cases where the input "day of month" is the last day of the input month and is less than or equal to the result "day of month" which is the case in question here.
What you need is an expression or better yet a "Function" (imagine that) that simulates a modified Add_Months Function, one where the above definition is changed to simply "If the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month." Then, subtract one day from whatever the result is.
By the way, the part before subtracting one day would also be the same definition as a VB/VBA Date Add("m", Num Months Inc, Source Date) call.
To do that, you would need an expression like: case when to_char(Source Date, 'DD') to_char(last_day(add_months(Source Date, Num Months)), 'DD') then add_months(Source Date, Num Months) - 1 else to_date ( to_char(add_months(Source Date, Num Months), 'MM) '/' to_char(Source Date, 'DD') '/' to_char(add_months(Source Date, Num Months), 'YYYY) 'MM/DD/YYYY') ) - 1 end If you wrote it into a Function, you could eliminate all those duplicate "add_months(Source Date, Num Months)" calls with a local variable not to mention embedding that monster of a Case Expression every time you needed it.
(so atleast with the current versions releases, add_months is more reliable) first of all this interval concept first time i am seeing it. fine if it doesnt work for the below select to_date('', 'DD-MM-YYYY') interval '1' year from dual; then i have two questions: 1) what is the difference between adding interval and add_months? It is not like "a week" Tell me, *what is a month* A year even is ambigous - years do not have the same number of days..
Perhaps I missed something in my hurry, but a function based on the next coding wordks for me: declare v_date date; v_bool boolean := FALSE; begin v_date := to_date('&datum','DDMMYYYY'); if v_date = last_day(v_date) then v_date := v_date - 1; v_bool := TRUE; end if; v_date := add_months(v_date, &aantal); if v_bool then if v_date = last_day(v_date) then null; else v_date := v_date 1; end if; end if; dbms_output.put_line(to_char(v_date, 'DDMMYYYY')); end; @Ron: Your function does solves the "bug" with the "design" of Add Months not making the Result Day of Month = Source Day of Month when Source Date = Last Day of Source Date's Month and Result Date's Month has more Days than Source Date's Month.
However: a) It doesn't solve the Original Poster's Q, which is to make the Result Day of Month 1 Day less than the Source Date's Day of Month (even when not in end-of-month scenarios).
What I find really brain-wracking is this: select add_months(add_months(to_date('30-jul-2005'), -1), 1) nd from dual; ND --------- 31-JUL-05 1 row selected. Kind regards, Ralf select to_date('28/02/2007','dd/mm/yyyy') 365*rownum from all_users; TO_DATE(' --------- 28-FEB-08 27-FEB-09 27-FEB-10 27-FEB-11 27-FEB-12 26-FEB-13 26-FEB-14 26-FEB-15 26-FEB-16 25-FEB-17 25-FEB-18 25-FEB-19 25-FEB-20 24-FEB-21 24-FEB-22 24-FEB-23 24-FEB-24 23-FEB-25 23-FEB-26 23-FEB-27 23-FEB-28 22-FEB-29 22-FEB-30 22-FEB-31 22-FEB-32 21-FEB-33 21-FEB-34 21-FEB-35 21-FEB-36 20-FEB-37 20-FEB-38 20-FEB-39 20-FEB-40 19-FEB-41 19-FEB-42 35 rows selected.
Tom I know adding 365 days is not right and I intended to explain to original poster (who wanted to jump from 28th Feb 2007 to 28th Feb 2008 ) was not calendar-friendly , rather plain arithmetic.Kind Regards, Vijay November 17, 2005 - am UTC add_months, as documented and by definition, returns the last_day of the resulting month if you input the last_day of a month to begin with.