Casting Substring to Int for Where Filter 1

[larrydavid]

Hello,

I'm about to pull out my last grey hair (of which I have none left but I'll find one to pull out). I have this stored procedure that is comparing dates, one that is a varchar in one table and one that is an int (lc.Current_Year) in another. I've been able to CAST varchars to ints in the past but for some reason (which I cannot figure out) my substring function is not liking this CAST. I'm getting way more records returned than I should so I know the WHERE join isn't working. If anyone can put me out of my misery and show me what I'm overlooking I would greatly appreciate it.

Thanks,
Larry

ALTER PROCEDURE [dbo].udsp_Test_Detail 
@tin AS varchar(9),
@msa AS varchar(50),
@currentYear AS int,
@currentMonth AS int
AS
BEGIN
	SET NOCOUNT ON;

SELECT DISTINCT
	lc.database_msa AS [MSA - CC], 
	tc.COMPID AS TIN, 
	lc.COMPANY, 
	tc.CLAIMNO AS CLAIM, 
	tc.RECNUM, 
	lc.Current_Year, 
	lc.Current_Month, 
	lc.Current_MonthName, 
	lc.Average_Cost
FROM         
	TEST_METRICS_LINE_CHART lc INNER JOIN
    TEST_CLAIMS tc ON lc.tin9 = tc.PROVID 
WHERE     
	lc.tin = '123456789' AND									
    lc.database_msa = 'TEST_LOCATION_1' AND
    lc.Current_Year = CAST(substring(tc.PDDATE, 1,4) AS INT) AND
    lc.Current_Month = CAST(substring(tc.PDDATE, 6,1) AS INT)  
END

Here is some output:

TEST_LOCATION_1	951683892	COMPANY1   	600518120	12347249	2012	1	Jan 2012	24401.25	11807.43	0.483886	6	4066.875	1967.905	0.00	0.00	0	0.00	NULL	100.00	0.00	NULL
TEST_LOCATION_1	951683892	COMPANY1      	600519190	12347250	2012	1	Jan 2012	24401.25	11807.43	0.483886	6	4066.875	1967.905	0.00	0.00	0	0.00	NULL	100.00	0.00	0
TEST_LOCATION_1	951683892	COMPANY1        600521751	12346853	2011	9	Sep 2011	18999.99	8946.62	0.470875	11	1727.2718	813.3291	26.666	0.00	0	0.00	NULL	100.00	0.00	NULL
TEST_LOCATION_1	951683892	COMPANY1        600534247	12346389	2011	5	May 2011	49736.53	9911.57	0.199281	14	3552.6093	707.9693	17.64	0.00	0	0.00	NULL	100.00	0.00	NULL
TEST_LOCATION_1	951683892	COMPANY1        600534247	12346390	2011	5	May 2011	49736.53	9911.57	0.199281	14	3552.6093	707.9693	17.64	0.00	0	0.00	NULL	100.00	0.00	NULL
TEST_LOCATION_1	951683892	COMPANY1        600587419	12346228	2011	3	Mar 2011	8409.16	3328.11	0.395772	5	1681.832	665.622	14.28	0.00	0	1679.90	NULL	100.00	0.00	NULL
TEST_LOCATION_1	951683892	COMPANY1        600609595	12346822	2011	9	Sep 2011	18999.99	8946.62	0.470875	11	1727.2718	813.3291	26.66	0.00	0	0.00	NULL	100.00	0.00	NULL

 

[TomKane]

Hi,

Can I ask you what data type is the field tc.PDDATE?

Regards,

 

[larrydavid]

varchar

 

[SQLBill]

Why do you think it is not working?

Run this and let us know what it returns:

SELECT current_year, current_month
FROM TEST_METRICS_LINE_CHART

I'm guessing it will return at least 2011 and 2012 for the year as that is what the query is returning. Your WHERE is looking for matches; it's not filtering the data any further. For example, it isn't saying 'I want everything from TEST_CLAIMS where CAST(substring(tc.PDDATE, 1,4) AS INT) = 2012'

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[larrydavid]

Hi SQLBill,

Yep, got it. My mistake. I think I was looking at this too long. anyway, I went through it again and figured it out and it is working fine now. Thanks for your help.

Larry