November 24, 2011 at 8:57 am
I currently have a stored procedure that takes a datevalue, string format yyyymm and uses it to produce a list ot trade items from the lasst 12 months from.
It comparison is done between the tradedate which is varchar of format yyyymmdd
my procedure is as follows:
REATE PROCEDURE [prv].[sp_TrdISIN_List]
@Period char(6) -- YYYYMM of the trade
--,@Debug int = 0 -- For debugging
AS
SET NOCOUNT ON
DECLARE @MyPeriod INT
SET @MyPeriod = Convert(int,@Period)
SET @MyPeriod = CASE
WHEN @MyPeriod between 175301 and 999912 and
@MyPeriod%100 between 1 and 12
THEN convert(char(8),dateadd(mm,(((@MyPeriod/100)-1900)*12)+(@MyPeriod%100),-1),112)
END
PRINT @MyPeriod
; WITH TradeA (ISIN, SecurityName, AssetClassCode, RowNbr) AS
(
SELECT TC.ISIN, TC.SecurityName, IT.AssetClassCode, RowNbr=ROW_NUMBER() OVER (PARTITION BY tC.ISIN ORDER BY tC.SecurityName DESC)
FROM hst.TDCR TC
JOIN [ref].[Map_InstrumentType] IT
ON TC.InstrumentType = IT.InstrumentType
AND IT.DataSourceName = 'CRTS'
WHERE TC.ISIN IS NOT NULL
AND TC.ISIN <>' '
AND convert(datetime,TC.TradeDate) > DATEADD(MM,-12,@MyPeriod)
)
, TradeB (ISIN, SecurityName, AssetClassCode, RowNbr) AS
(
SELECT TB.ISIN, TB.SecurityName, IT.AssetClassCode, RowNbr=ROW_NUMBER() OVER (PARTITION BY tb.ISIN ORDER BY tb.TradeDate, tb.SecurityName DESC)
FROM hst.TDSD TB
JOIN [ref].[Map_InstrumentType] IT
ON tb.InstrumentType = it.SourceInstrumentCode
AND IT.DataSourceName = 'SCD'
WHERE tb.ISIN IS NOT NULL
AND convert(datetime,TB.TradeDate) > DATEADD(MM,-12,@MyPeriod)
)
, TradeC (ISIN, SecurityName, AssetClassCode, RowNbr) AS
(
SELECT TL.InstrumentISIN AS ISIN, TL.InstrumentName AS SecurityName, IT.AssetClassCode, RowNbr=ROW_NUMBER() OVER (PARTITION BY TL.InstrumentISIN ORDER BY TL.TradeDate, TL.InstrumentName DESC)
FROM hst.TDSL TL
JOIN [ref].[Map_InstrumentType] IT
ON TL.InstrumentType = IT.SourceInstrumentCode
AND IT.DataSourceName = 'BS&C'
WHERE TL.InstrumentISIN IS NOT NULL
AND convert(datetime,TL.TradeDate) > DATEADD(MM,-12,@MyPeriod)
)
SELECT ISIN, SecurityName as InstrumentName, AssetClassCode
FROM TradeA
WHERE RowNbr=1
AND AssetClassCode NOT IN ('R', 'P', 'H', 'F', 'E','C')
AND NOT LEFT(SecurityName,1) LIKE '*%'
AND ISIN LIKE '[^0-9]'
AND LEN(ISIN) >= 12
UNION
SELECT ISIN, SecurityName as InstrumentName,AssetClassCode
FROM TradeB
WHERE RowNbr=1
AND AssetClassCode NOT IN ('R', 'P', 'H', 'F', 'E','C')
AND NOT LEFT(SecurityName,1) LIKE '*%'
AND ISIN LIKE '[^0-9]'
AND LEN(ISIN) >= 12
UNION
SELECT ISIN, SecurityName as InstrumentName,AssetClassCode
FROM TradeC
WHERE RowNbr=1
AND AssetClassCode NOT IN ('R', 'P', 'H', 'F', 'E','C')
AND NOT LEFT(SecurityName,1) LIKE '*%'
AND ISIN LIKE '[^0-9]'
AND LEN(ISIN) >= 12
WHEN I run the procedure with the following parameters
EXEC [sp_TrdISIN_List] '201103'
i get the following error:
Msg 8115, Level 16, State 2, Procedure sp_TrdISIN_List, Line 35
Arithmetic overflow error converting expression to data type datetime.
any ideas?
November 24, 2011 at 9:14 am
You're using "your" proc incorrectly.
DECLARE @MyPeriod INT
SET @MyPeriod = CONVERT(INT, '201103')
SET @MyPeriod = CASE WHEN @MyPeriod BETWEEN 175301 AND 999912 AND @MyPeriod % 100 BETWEEN 1 AND 12
THEN CONVERT(CHAR(8),DATEADD(mm, (((@MyPeriod / 100) - 1900) * 12) + (@MyPeriod % 100), - 1),112)
END
SELECT @MyPeriod
The above returns an INT of "20110331".
In your WHERE clause you're then doing the following comparison: -
AND convert(datetime,TC.TradeDate) > DATEADD(MM,-12,@MyPeriod)
Try executing that DATEADD and see what happens -
DECLARE @MyPeriod INT
SET @MyPeriod = CONVERT(INT, '201103')
SET @MyPeriod = CASE WHEN @MyPeriod BETWEEN 175301 AND 999912 AND @MyPeriod % 100 BETWEEN 1 AND 12
THEN CONVERT(CHAR(8),DATEADD(mm, (((@MyPeriod / 100) - 1900) * 12) + (@MyPeriod % 100), - 1),112)
END
SELECT DATEADD(MM,-12,@MyPeriod)
Same error? Thought so. Try: -
DECLARE @MyPeriod INT
SET @MyPeriod = CONVERT(INT, '201103')
SET @MyPeriod = CASE WHEN @MyPeriod BETWEEN 175301 AND 999912 AND @MyPeriod % 100 BETWEEN 1 AND 12
THEN CONVERT(CHAR(8),DATEADD(mm, (((@MyPeriod / 100) - 1900) * 12) + (@MyPeriod % 100), - 1),112)
END
SELECT DATEADD(MM,-12,CONVERT(DATETIME,CONVERT(VARCHAR(8),@MyPeriod)))
November 24, 2011 at 4:31 pm
Hi sorry for the late reply. got pulled into something else. i actually got round to doing what you proposed and it worked. Many thanks. the dates issues have been bugging me all week. much appreciated.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply