November 20, 2008 at 5:14 pm
Hi,
why doesn't this bring back Dec 31?
I am building an Amortized Table of Loans, and this particular one is giving me problems... so that by the time I reach the Maturity Date, I am off by a day and thus my function returns false cause they don't equal...
SELECT DATEADD(MM, 1, 'Nov 30 2008 12:00AM')
SELECT DATEADD(MM, 1, '2009-07-23 00:00:00.000')
This is crazy, I went back and looked and for dates anywhere within a month return back ok, it's just those that have dates within the last day of the month that are screwing up???? HELP...lol
November 20, 2008 at 5:18 pm
You need to add 1 month to LAST DAY of a month, not to 30th day of month.
_____________
Code for TallyGenerator
November 20, 2008 at 5:23 pm
I am confused... the dates come to me as is... the one with a Nov Date started it's journey from
2008-10-31 00:00:00.000, and it came out right to Nov 30 doing the DATEADD...
could you show me example...
November 20, 2008 at 5:33 pm
Let's ask a different question first. Are the dates you are using supposed to be the last day of every month?
November 20, 2008 at 5:34 pm
What kind of example you're asking for?
You don't need examples.
You need right formula.
What you want to get back?
Last day of next mont?
So, ask for it.
Not for a day 1 month after current date.
But for last day of the next month.
Think what "last day" really means, how you'd explain it to a 2 years old kid and implement this explanation in your formula.
_____________
Code for TallyGenerator
November 20, 2008 at 5:58 pm
I am getting dates that can start from the first, and be due anytime inbetween... not only that, they can be days apart, years apart...
I guess i made the wrong assumption about the DATEADD FUNCTION... I thought if i passed in the last day of one month it would return the last day of the next month...
here is my function, and here is me calling the function using an UPDATE STATEMENT...
@PRIOR_PAY_DATE=@NEXT_PAY_DATE
,
-- SO THE 2ND TO LAST ROW AND LAST ROW OF ID IS TRUE, BUT THE LAST ROW ISN'T WHAT MATTERS...
@NEXT_PAY_DATE= [CALCULTD_NEXT_PAY_DATE]=[dbo].[fn_AM_CALC_NEXT_PAY_DATE](@PRIOR_FMULTI, @PRIOR_FREQ,
CASE
WHEN @PrevGrpCnt = 1
THEN [A].[NEXT_PAYMENT_DATE]
ELSE @PRIOR_PAY_DATE
, @PrevGrpCnt)
,
@PRIOR_FMULTI= [A].[PMT_FREQ_MULT]
,
@PRIOR_FREQ= [A].[PMT_FREQ]
ALTER FUNCTION [dbo].[fn_AM_CALC_NEXT_PAY_DATE]
(
-- Add the parameters for the function here
@PMT_FREQ_MULTCHAR(1),
@PMT_FREQINT,
@NEXT_PAY_DATEDATETIME,
@RECCNTINT
)
RETURNSDATETIME
AS
BEGIN
-- Declare the return variable here
DECLARE @dRetValDATETIME
-- Add the T-SQL statements to compute the return value here
IF @RECCNT = 1
BEGIN
SET @dRetVal = @NEXT_PAY_DATE
GOTO OK
END
SET @dRetVal =
(CASE @PMT_FREQ_MULT
WHEN 'M' THEN DATEADD(MONTH,@PMT_FREQ, @NEXT_PAY_DATE)
WHEN 'D' THEN DATEADD(DAY,@PMT_FREQ, @NEXT_PAY_DATE)
WHEN 'Y' THEN DATEADD(YEAR,@PMT_FREQ, @NEXT_PAY_DATE)
END)
OK:
-- Return the result of the function
RETURN @dRetVal
END
GO
November 20, 2008 at 6:13 pm
DATEADD function does exactly what you ask it to do.
You ask to add 1 month to 30 Nov - it returns absolutely correct answer: 30 Dec.
If you need last day of next month you need to ask for last day of next month.
_____________
Code for TallyGenerator
November 20, 2008 at 6:22 pm
Sergiy, I don't think he is looking for the last day of each month. To be honest, I am not sure if he knows the right question to ask. Heck, I'm having a slightly difficult time trying to ask the right question to get what he is looking for as well.
I think the best way to figure this out is to visualize what you are looking for. Provide us with some example date ranges, and I'd start in January and go about 4 or 5 months with each. Dates early in the month, middle of the month, and toward the end of the month (especially ones where the different lengths of the months can cause issues as you have seen with the DATEADD function).
November 20, 2008 at 6:47 pm
I guess it's explained here:
john.steinbeck (11/20/2008)
I am confused... the dates come to me as is... the one with a Nov Date started it's journey from2008-10-31 00:00:00.000, and it came out right to Nov 30 doing the DATEADD...
The trick is
DATEADD(MM, 2, '2008-10-31')
not equal to
DATEADD(MM, 1, DATEADD(MM, 1, '2008-10-31') )
There is nothing wrong with DATEDD.
It's our calendar.
Same story when you add 4 years to 29 Feb:
DATEADD(YY, 4, '2008-02-29') = '2012-02-29'
But if you add 4 times by 1 year you'll end up with '2012-02-28'
What if "journey" starts from 30 Oct?
Next month date will be 30 Nov.
What do you want to get as next one after this?
If you wish last DATEADD calculation to take into consideration where the journey started from you need to pass this bit of information to it.
_____________
Code for TallyGenerator
November 20, 2008 at 7:17 pm
I was afraid of this... I have over 170,000 loans, that when ballooned out with all the payment dates is over 4million rows...
I have the the MaturityDate to work with and the PreviousPaymentDate...
As you see with the CASE Statement, I am giving the Frequency to use with DD, MM, YY...
Any Scenario you can think of, I have it...
So somehow I have to know when I need the last Day of the Month to appear... not liking this...lol
I guess when I get to work I can post some data to work with...
November 20, 2008 at 7:37 pm
That will probably help. What I am looking for is a pattern, if one exists.
November 20, 2008 at 7:48 pm
jsteinbeck (11/20/2008)
I have the the MaturityDate to work with and the PreviousPaymentDate...As you see with the CASE Statement, I am giving the Frequency to use with DD, MM, YY...
Any Scenario you can think of, I have it...
OK.
It has nothing to do with DATEADD, nothing to do with calendar.
Reason of your error is your lack of understanding of the business process.
For all loans Maturity Date is ALWAYS calculated from Init Date for the loan, not from the date of last payment.
Payments are placed on time line according to the schedule and shifted back or forward depending on:
- end of month;
- weekend;
- public holiday;
- bank holiday;
- whatever else what can shift the payment.
Shifting of last payment should NEVER affect the schedule for next payment.
You are just doing wrong thing, that's why you're getting wrong results.
_____________
Code for TallyGenerator
November 20, 2008 at 7:59 pm
And, I actually have to agree with Sergiy, to a point. There is also additional information that may change what you are doing. Is this an amoritization schedule, or are you trying to compute actuals. There is a difference. Depending on when a payment is actually made will affect the amount of the payment applied to principle and interest.
November 20, 2008 at 8:55 pm
I think patterns only exist within each loan, so I have to accommodate for all...
I think i got it...
I first created this function to return the last day of any month...
Then I altered my previous function I posted to see if the date i pass in matches the last day of the month, if it does
i add 1 day to get into the next month and then get the last day of that month.... if not then continue as I normally would...
SELECT
[dbo].[fn_AM_CALC_NEXT_PAY_DATE]('M',17,CAST('Nov 30 2008' AS DATETIME),3) AS TESTDATE
ALTER FUNCTION [dbo].[fn_AM_IsLastDay]
(
-- Add the parameters for the function here
@dEVALDATEDATETIME
)
RETURNSDATETIME
AS
BEGIN
-- Declare the return variable here
DECLARE @dRetValDATETIME
-- Add the T-SQL statements to compute the return value here
--SET @dRetVal= DATEADD(M, DATEDIFF(M,0,@dEVALDATE)+1,0)
SET @dRetVal= DATEADD(HH,-24,DATEADD(M, DATEDIFF(M,0,@dEVALDATE)+1,0))
-- Return the result of the function
RETURN @dRetVal
END
GO
ALTER FUNCTION [dbo].[fn_AM_CALC_NEXT_PAY_DATE]
(
-- Add the parameters for the function here
@PMT_FREQ_MULTCHAR(1),
@PMT_FREQINT,
@NEXT_PAY_DATEDATETIME,
@RECCNTINT
)
RETURNSDATETIME
AS
BEGIN
-- Declare the return variable here
DECLARE @dRetValDATETIME
DECLARE @dEVALDATEDATETIME
-- Add the T-SQL statements to compute the return value here
IF @RECCNT = 1
BEGIN
SET @dRetVal = @NEXT_PAY_DATE
GOTO OK
END
-- SEE IF THIS DATE IS THE LAST DAY OF MONTH, IF SO THEN COMPARE IF IS THEN RETURN LAST DAY...
IF @PMT_FREQ_MULT <> 'D'
BEGIN
SET @dEVALDATE =[dbo].[fn_AM_IsLastDay](@NEXT_PAY_DATE)
IF @NEXT_PAY_DATE = @dEVALDATE
BEGIN
SET @dRetVal=
(
CASE @PMT_FREQ_MULT
WHEN 'M' THEN [dbo].[fn_AM_IsLastDay](DATEADD(MONTH, @PMT_FREQ, @dEVALDATE))
WHEN 'Y' THEN [dbo].[fn_AM_IsLastDay](DATEADD(YEAR, @PMT_FREQ, @dEVALDATE))
END
)
SET @dEVALDATE=[dbo].[fn_AM_IsLastDay](@dRetVal)
END
ELSE
BEGIN
SET @dRetVal=
(
CASE @PMT_FREQ_MULT
WHEN 'M' THEN DATEADD(MONTH, @PMT_FREQ, @NEXT_PAY_DATE)
WHEN 'Y' THEN DATEADD(YEAR ,@PMT_FREQ, @NEXT_PAY_DATE)
END
)
END
END
ELSE
BEGIN
SET @dRetVal=
(
CASE @PMT_FREQ_MULT
WHEN 'M' THEN DATEADD(MONTH, @PMT_FREQ, @NEXT_PAY_DATE)
WHEN 'D' THEN DATEADD(DAY , @PMT_FREQ, @NEXT_PAY_DATE)
WHEN 'Y' THEN DATEADD(YEAR ,@PMT_FREQ, @NEXT_PAY_DATE)
END
)
END
OK:
-- Return the result of the function
RETURN @dRetVal
END
GO
November 20, 2008 at 9:01 pm
So, after all of this, you are actually trying to get the end of each month?
Viewing 15 posts - 1 through 15 (of 40 total)
You must be logged in to reply to this topic. Login to reply