November 21, 2008 at 2:00 pm
I have a Million rows with dates, so I don't know what dates to add here for you...
If someone could help me populate the table with random dates to test this out would be great...
[PMT_FREQ] is any number for the DATEADD FUNCTION
[PMT_FREQ_MULT] is only M, Y, D
[TBL_SOURCEID] is only 1
the problem is when I added the function [dbo].[fn_AM_Eval_Last_n_Matur_DATE] i get this error
Msg 242, Level 16, State 3, Procedure fn_AM_Eval_Last_n_Matur_DATE, Line 26
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
I can run this test SELECT [dbo].[fn_AM_Eval_Last_n_Matur_DATE](GETDATE(),DATEADD(DAY,2,GETDATE()))
with no issues... could i have bad data? i looked for nulls, 0 and found nothing...
what it's doing is taking the last_payment_date and the maturity_date... if the days don't match
then the last_payment_date will take on the maturity_date's DAY only, the rest will stay the same
for the last_payment_date... (this is what you have to do when you get dirty data...lol) then I use
the last_payment_date to get the [A].[NEXT_PAYMENT_DATE] value using the function [dbo].[fn_AM_CALC_NEXT_PAY_DATE]...
I will be on later tonight when I get to my Sister's... (14 kids...lol)
Thanks a million...
John
CREATE TABLE [dbo].[Amortization]
(
,[PMT_FREQ]INT
,[PMT_FREQ_MULT]CHAR(1)
,[LAST_PAYMENT_DATE]DATETIME
,[NEXT_PAYMENT_DATE]DATETIME
,[MATURITY_DATE]DATETIME
,[TBL_SOURCEID]CHAR
)
DECLARE @LAST_PAY_DATEDATETIME
UPDATE[A] --[dbo].[Amortization]
SET
-- HAVE TO GET THE LAST PAY DATE TO MATCH WITH THE MATURITY DATE...
@LAST_PAY_DATE= [A].[LAST_PAYMENT_DATE]= [dbo].[fn_AM_Eval_Last_n_Matur_DATE]([A].[LAST_PAYMENT_DATE], [A].[MATURITY_DATE]),
[A].[NEXT_PAYMENT_DATE]= [dbo].[fn_AM_CALC_NEXT_PAY_DATE]([A].[PMT_FREQ_MULT], [A].[PMT_FREQ], @LAST_PAY_DATE, 0)
--SELECT *
FROM
[dbo].[Amortization] [A]
WHERE
[A].[TBL_SOURCEID] = '1'
ALTER FUNCTION [dbo].[fn_AM_Eval_Last_n_Matur_DATE]
(
-- Add the parameters for the function here
@dLAST_PAY_DATEDATETIME,
@dMATURITY_DATEDATETIME
)
RETURNSDATETIME
AS
BEGIN
-- Declare the return variable here
DECLARE @dRetValDATETIME
DECLARE @iDAYINT
-- Add the T-SQL statements to compute the return value here
IF DAY(@dLAST_PAY_DATE) = DAY(@dMATURITY_DATE)
BEGIN
SET @dRetVal = @dLAST_PAY_DATE
END
ELSE
BEGIN
SET @dRetVal = CONVERT(DATETIME, CAST(YEAR(@dLAST_PAY_DATE) AS VARCHAR) + '/' + CAST(MONTH(@dLAST_PAY_DATE) AS VARCHAR) + '/' + CAST(DAY(@dMATURITY_DATE) AS VARCHAR),110)
END
-- Return the result of the function
RETURN @dRetVal
END
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
November 21, 2008 at 2:28 pm
Your problem is here:
SET @dRetVal = CONVERT(DATETIME, CAST(YEAR(@dLAST_PAY_DATE) AS VARCHAR) + '/' + CAST(MONTH(@dLAST_PAY_DATE) AS VARCHAR) + '/' + CAST(DAY(@dMATURITY_DATE) AS VARCHAR),110)
You are taking a date, say 2008-11-21 00:00:00.000, and creating a string date that looks like this, 2008/11/21, and trying to convert it back to datetime value. By the way, you don't need the ,112 at the end of the convert. You only need that when you are converting FROM a datetime value to a character string.
If the pupose of this is to drop the time portion off @dLAST_PAY_DATE, converting back to a datetime is just going to make it all zeros and an easier way to do that is this: dateadd(dd, datediff(dd, 0, @dLAST_PAY_DATE), 0).
November 22, 2008 at 11:59 am
You know what else it is... if i take a months day say 31 and give it to a month with only 30 days, well it doesn't like that... so now I have to build some kind of function, to subtract or add to get the end of the month right...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply