SQLTeam.com | Weblogs | Forums

Help with a stored procedure


#1

I created a large query that works properly but I would like to make it into a stored procedure. When I do that though, I get the following error:

Msg 8114, Level 16, State 5, Procedure StdPrc_PL, Line 0
Error converting data type varchar to date.

Im not sure why. Can anyone please help with that??


Can't refer to too many users?
#2

I tried to post the query but cannot, it tells me that because I am a new user that I can only mention 2 users in a post? Does anyone have a suggestion as to how I may get around thin?


#3

I am suspecting that that is because the forum software interprets any word that starts with an @ symbol as reference to a user. Unfortunately, @ symbol is how you define variables in SQL as well.


#4

Just as the error message says, this error happens when you try to convert a VARCHAR column to a date/datetime. Usually that happens when you have a WHERE clause or a JOIN condition where you have string column/variable on one side of a logical operator and a DATE/DATETIME column/variable on the other side.

Look for places in your code where that type of logical evaluation exist.


#5

That is the issue James, Im declaring several variables in the code......


#6

I am not trying to convert anything though......in the statement I am using to execute the SP, dates are enclosed in single quotes (''). Does that inherently convert them?

I do have a join based on a date though?


#7

If you have a date enclosed in single quotes, you must be using it somewhere within the code, right? That something is probably comparing that to another column/variable which is of date/datetime type. When you do that, SQL server implicitly tries to convert your string to a date/datetime.


#8

yes, Im declaring it as a variable at the top of the code then calling it in the where clause


#9

Post your declaration and the where clause (or the whole code). Replace the @ symbol with something else - a symbol like $ or # that is not used in your code.


#10

ok, Ill do that now


#11

here is the query, all @ have been replaced with $:

declare $ContractStart1 date,
$ContractEnd1 date,
$PaidDateStart1 date,
$PaidDateEnd1 date,

		$ContractStart2 date,
		$ContractEnd2 date,
		$PaidDateStart2 date,
		$PaidDateEnd2 date,
		
		$ContractStart3 date,
		$ContractEnd3 date,
		$PaidDateStart3 date,
		$PaidDateEnd3 date,
		
		$HVMA2012 money,
		$NonHVMA2012 money,
		$HVMA2013 money,
		$NonHVMA2013 money,
		$HVMA2014 money,
		$NonHVMA2014 money,
		$HVMA2015 money,
		$NonHVMA2015 money,
		$HVMA2016 money, 
		$NonHVMA2016 money

set $ContractStart1 = '04/01/2011'
set $ContractEnd1 = '03/31/2012'
set $PaidDateStart1 = '04/01/2011'
set $PaidDateEnd1 = '04/30/2015'

set $ContractStart2 = '04/01/2012'
set $ContractEnd2 = '03/31/2013'
set $PaidDateStart2 = '04/01/2012'
set $PaidDateEnd2 = '04/30/2015'

set $ContractStart3 = '04/01/2013'
set $ContractEnd3 = '03/31/2014'
set $PaidDateStart3 = '04/01/2013'
set $PaidDateEnd3 = '04/30/2015'

set $HVMA2012 = '95.24'
set $NonHVMA2012 = '10.42'
set $HVMA2013 = '49.36'
set $NonHVMA2013 = '7.17'
set $HVMA2014 = '90.26'
set $NonHVMA2014 = '16.52'
set $HVMA2015 = '91.74'
set $NonHVMA2015 = '16.22'
set $HVMA2016 = '91.74'
set $NonHVMA2016 = '16.22'

/Period 1/
--Members - P1

select		$ContractStart1 as Contract_Start
			,	$ContractEnd1 as Contract_End
			,	SUM(membermonths) as All_Mems	
			
into		#Members1		
				
from		DW..Membermonths			
				
where		ReportingMonth between $ContractStart1 and $ContractEnd1	
			and PolicyNumber = '044MG'

--Premium - P1

select		$ContractStart1 as Contract_Start
			,	$ContractEnd1 as Contract_End		
			,	sum(BilledPremiumAmount) as Prem_Amt
			
into		#Premium1		

from		dw..PremiumBilling
 
where		PremiumMonth between $ContractStart1 and $ContractEnd1
			and InvoiceStatus = 'Billed'			
			and PolicyNumber = '044MG'
			
--Claims and CAP - P1

  --inpatient and outpatient												
select		$ContractStart1 as Contract_Start
			,	$ContractEnd1 as Contract_End
			,	sum(cl.NHPLiability) as Med_Amt	
			
into		#IP_OP1									
												
from		dw..ClaimLine cl 											
			inner join dw..ClaimHeader ch 
				on ch.ClaimID = cl.ClaimID											
																	
where		ServiceDateFrom between $ContractStart1 and $ContractEnd1											
			and ch.PaidDate between $PaidDateStart1 and $PaidDateEnd1											
			and ch.PolicyNumber = '044MG'										
			and ch.ClaimStatus in ('PAID','PAY')											
			and ch.ResubmittedClaimId = ''	
										 												
  --mental health
select		$ContractStart1 as Contract_Start
			,	$ContractEnd1 as Contract_End
			,	sum(cl.NHPLiability) as MH_Amt		
			
into		#MH1									
												
from		dw..MHClaimLine cl 											
			inner join dw..MHClaimHeader ch 
				on ch.ClaimID = cl.ClaimID											
																			
where		ServiceDateFrom between $ContractStart1 and $ContractEnd1											
			and ch.PaidDate between $PaidDateStart1 and $PaidDateEnd1						 				
			and ch.PolicyNumber = '044MG'
			and ch.ClaimStatus in ('PAID','PAY')											
			and ch.ResubmittedClaimId = ''
			
  --pharmacy													
select		$ContractStart1 as Contract_Start
			,	$ContractEnd1 as Contract_End
			,	sum(cl.NHPLiability) as Rx_Amt	
			
into		#Pharmacy1										
												
from		dw..PharmacyService cl		
												
where		cl.ServiceDateFrom between $ContractStart1 and $ContractEnd1											
			and cl.LagPaidDate between $PaidDateStart1 and $PaidDateEnd1											
			and cl.PolicyNumber = '044MG'											
			and cl.ClaimStatus in ('PAID','PAY')											
			and cl.ResubmittedClaimId = ''	
														
  --capitation										
select		$ContractStart1 as Contract_Start
			,	$ContractEnd1 as Contract_End
			,	sum(										
				case 										
					when pcpnetworkid = 2 and reportingmonth between '01/01/2012' and '12/01/2012' then (membermonths*$HVMA2012)									
					when pcpnetworkid = 2 and reportingmonth between '01/01/2013' and '12/01/2013' then (membermonths*$HVMA2013) 									
					when pcpnetworkid = 2 and reportingmonth between '01/01/2014' and '12/01/2014' then (membermonths*$HVMA2014)									
					when pcpnetworkid = 2 and reportingmonth between '01/01/2015' and '12/01/2015' then (membermonths*$HVMA2015)
					when pcpnetworkid = 2 and reportingmonth between '01/01/2016' and '12/01/2016' then (membermonths*$HVMA2016)									
					when pcpnetworkid <> 2 and reportingmonth between '01/01/2012' and '12/01/2012' then (membermonths*$NonHVMA2012)									
					when pcpnetworkid <> 2 and reportingmonth between '01/01/2013' and '12/01/2013' then (membermonths*$NonHVMA2013)									
					when pcpnetworkid <> 2 and reportingmonth between '01/01/2014' and '12/01/2014' then (membermonths*$NonHVMA2014)									
					when pcpnetworkid <> 2 and reportingmonth between '01/01/2015' and '12/01/2015' then (membermonths*$NonHVMA2015)
					when pcpnetworkid <> 2 and reportingmonth between '01/01/2016' and '12/01/2016' then (membermonths*$NonHVMA2016)								
				end										
				) 										
			as Cap_Amt										

into		#Cap1
												
from		dw..membermonths											
												
where		reportingmonth between $ContractStart1 and $ContractEnd1										
			and PolicyNumber = '044MG'

/Period 2/
--Members - P2

select		$ContractStart2 as Contract_Start
			,	$ContractEnd2 as Contract_End
			,	SUM(membermonths) as All_Mems	
			
into		#Members2		
				
from		DW..Membermonths			
				
where		ReportingMonth between $ContractStart2 and $ContractEnd2	
			and PolicyNumber = '044MG'

--Premium - P2

select		$ContractStart2 as Contract_Start
			,	$ContractEnd2 as Contract_End		
			,	sum(BilledPremiumAmount) as Prem_Amt
			
into		#Premium2		

from		dw..PremiumBilling
 
where		PremiumMonth between $ContractStart2 and $ContractEnd2
			and InvoiceStatus = 'Billed'			
			and PolicyNumber = '044MG'
			
--Claims and CAP - P2

  --inpatient and outpatient												
select		$ContractStart2 as Contract_Start
			,	$ContractEnd2 as Contract_End
			,	sum(cl.NHPLiability) as Med_Amt	
			
into		#IP_OP2								
												
from		dw..ClaimLine cl 											
			inner join dw..ClaimHeader ch 
				on ch.ClaimID = cl.ClaimID											
																	
where		ServiceDateFrom between $ContractStart2 and $ContractEnd2											
			and ch.PaidDate between $PaidDateStart2 and $PaidDateEnd2											
			and ch.PolicyNumber = '044MG'										
			and ch.ClaimStatus in ('PAID','PAY')											
			and ch.ResubmittedClaimId = ''	
										 												
  --mental health
select		$ContractStart2 as Contract_Start
			,	$ContractEnd2 as Contract_End
			,	sum(cl.NHPLiability) as MH_Amt		
			
into		#MH2									
												
from		dw..MHClaimLine cl 											
			inner join dw..MHClaimHeader ch 
				on ch.ClaimID = cl.ClaimID											
																			
where		ServiceDateFrom between $ContractStart2 and $ContractEnd2											
			and ch.PaidDate between $PaidDateStart2 and $PaidDateEnd2						 				
			and ch.PolicyNumber = '044MG'
			and ch.ClaimStatus in ('PAID','PAY')											
			and ch.ResubmittedClaimId = ''
			
  --pharmacy													
select		$ContractStart2 as Contract_Start
			,	$ContractEnd2 as Contract_End
			,	sum(cl.NHPLiability) as Rx_Amt	
			
into		#Pharmacy2									
												
from		dw..PharmacyService cl		
												
where		cl.ServiceDateFrom between $ContractStart2 and $ContractEnd2											
			and cl.LagPaidDate between $PaidDateStart2 and $PaidDateEnd2											
			and cl.PolicyNumber = '044MG'											
			and cl.ClaimStatus in ('PAID','PAY')											
			and cl.ResubmittedClaimId = ''	
														
  --capitation										
select		$ContractStart2 as Contract_Start
			,	$ContractEnd2 as Contract_End
			,	sum(										
				case 										
					when pcpnetworkid = 2 and reportingmonth between '01/01/2012' and '12/01/2012' then (membermonths*$HVMA2012)									
					when pcpnetworkid = 2 and reportingmonth between '01/01/2013' and '12/01/2013' then (membermonths*$HVMA2013) 									
					when pcpnetworkid = 2 and reportingmonth between '01/01/2014' and '12/01/2014' then (membermonths*$HVMA2014)									
					when pcpnetworkid = 2 and reportingmonth between '01/01/2015' and '12/01/2015' then (membermonths*$HVMA2015)
					when pcpnetworkid = 2 and reportingmonth between '01/01/2016' and '12/01/2016' then (membermonths*$HVMA2016)									
					when pcpnetworkid <> 2 and reportingmonth between '01/01/2012' and '12/01/2012' then (membermonths*$NonHVMA2012)									
					when pcpnetworkid <> 2 and reportingmonth between '01/01/2013' and '12/01/2013' then (membermonths*$NonHVMA2013)									
					when pcpnetworkid <> 2 and reportingmonth between '01/01/2014' and '12/01/2014' then (membermonths*$NonHVMA2014)									
					when pcpnetworkid <> 2 and reportingmonth between '01/01/2015' and '12/01/2015' then (membermonths*$NonHVMA2015)
					when pcpnetworkid <> 2 and reportingmonth between '01/01/2016' and '12/01/2016' then (membermonths*$NonHVMA2016)									
				end										
				) 										
			as Cap_Amt										

into		#Cap2
												
from		dw..membermonths											
												
where		reportingmonth between $ContractStart2 and $ContractEnd2										
			and PolicyNumber = '044MG'	

/Period 3/
--Members - P3

select		$ContractStart3 as Contract_Start
			,	$ContractEnd3 as Contract_End
			,	SUM(membermonths) as All_Mems	
			
into		#Members3		
				
from		DW..Membermonths			
				
where		ReportingMonth between $ContractStart3 and $ContractEnd3	
			and PolicyNumber = '044MG'

--Premium - P3

select		$ContractStart3 as Contract_Start
			,	$ContractEnd3 as Contract_End		
			,	sum(BilledPremiumAmount) as Prem_Amt
			
into		#Premium3		

from		dw..PremiumBilling
 
where		PremiumMonth between $ContractStart3 and $ContractEnd3
			and InvoiceStatus = 'Billed'			
			and PolicyNumber = '044MG'
			
--Claims and CAP - P3

  --inpatient and outpatient												
select		$ContractStart3 as Contract_Start
			,	$ContractEnd3 as Contract_End
			,	sum(cl.NHPLiability) as Med_Amt	
			
into		#IP_OP3								
												
from		dw..ClaimLine cl 											
			inner join dw..ClaimHeader ch 
				on ch.ClaimID = cl.ClaimID											
																	
where		ServiceDateFrom between $ContractStart3 and $ContractEnd3											
			and ch.PaidDate between $PaidDateStart3 and $PaidDateEnd3											
			and ch.PolicyNumber = '044MG'										
			and ch.ClaimStatus in ('PAID','PAY')											
			and ch.ResubmittedClaimId = ''	
										 												
  --mental health
select		$ContractStart3 as Contract_Start
			,	$ContractEnd3 as Contract_End
			,	sum(cl.NHPLiability) as MH_Amt		
			
into		#MH3									
												
from		dw..MHClaimLine cl 											
			inner join dw..MHClaimHeader ch 
				on ch.ClaimID = cl.ClaimID											
																			
where		ServiceDateFrom between $ContractStart3 and $ContractEnd3											
			and ch.PaidDate between $PaidDateStart3 and $PaidDateEnd3						 				
			and ch.PolicyNumber = '044MG'
			and ch.ClaimStatus in ('PAID','PAY')											
			and ch.ResubmittedClaimId = ''
			
  --pharmacy													
select		$ContractStart3 as Contract_Start
			,	$ContractEnd3 as Contract_End
			,	sum(cl.NHPLiability) as Rx_Amt	
			
into		#Pharmacy3									
												
from		dw..PharmacyService cl		
												
where		cl.ServiceDateFrom between $ContractStart3 and $ContractEnd3											
			and cl.LagPaidDate between $PaidDateStart3 and $PaidDateEnd3											
			and cl.PolicyNumber = '044MG'											
			and cl.ClaimStatus in ('PAID','PAY')											
			and cl.ResubmittedClaimId = ''	
														
  --capitation										
select		$ContractStart3 as Contract_Start
			,	$ContractEnd3 as Contract_End
			,	sum(										
				case 										
					when pcpnetworkid = 2 and reportingmonth between '01/01/2012' and '12/01/2012' then (membermonths*$HVMA2012)									
					when pcpnetworkid = 2 and reportingmonth between '01/01/2013' and '12/01/2013' then (membermonths*$HVMA2013) 									
					when pcpnetworkid = 2 and reportingmonth between '01/01/2014' and '12/01/2014' then (membermonths*$HVMA2014)									
					when pcpnetworkid = 2 and reportingmonth between '01/01/2015' and '12/01/2015' then (membermonths*$HVMA2015)
					when pcpnetworkid = 2 and reportingmonth between '01/01/2016' and '12/01/2016' then (membermonths*$HVMA2016)									
					when pcpnetworkid <> 2 and reportingmonth between '01/01/2012' and '12/01/2012' then (membermonths*$NonHVMA2012)									
					when pcpnetworkid <> 2 and reportingmonth between '01/01/2013' and '12/01/2013' then (membermonths*$NonHVMA2013)									
					when pcpnetworkid <> 2 and reportingmonth between '01/01/2014' and '12/01/2014' then (membermonths*$NonHVMA2014)									
					when pcpnetworkid <> 2 and reportingmonth between '01/01/2015' and '12/01/2015' then (membermonths*$NonHVMA2015)
					when pcpnetworkid <> 2 and reportingmonth between '01/01/2016' and '12/01/2016' then (membermonths*$NonHVMA2016)									
				end										
				) 										
			as Cap_Amt										

into		#Cap3
												
from		dw..membermonths											
												
where		reportingmonth between $ContractStart3 and $ContractEnd3										
			and PolicyNumber = '044MG'	

/final reports/
--Period 1
select * from #Members1
select * from #Premium1

select m.Contract_Start, m.Contract_End, (m.Med_Amt + mh.MH_Amt + rx.Rx_Amt + c.Cap_Amt) as Total

from #IP_OP1 m 

inner join #MH1 mh 
on m.Contract_Start = MH.Contract_Start and m.Contract_End = MH.Contract_End

inner join #Pharmacy1 rx
on m.Contract_Start = rx.Contract_Start and m.Contract_End = rx.Contract_End

inner join #Cap1 c
on m.Contract_Start = c.Contract_Start and m.Contract_End = c.Contract_End

	--Period 2
select * from #Members2
select * from #Premium2

select m.Contract_Start, m.Contract_End, (m.Med_Amt + mh.MH_Amt + rx.Rx_Amt + c.Cap_Amt) as Total

from #IP_OP2 m 

inner join #MH2 mh 
on m.Contract_Start = MH.Contract_Start and m.Contract_End = MH.Contract_End

inner join #Pharmacy2 rx
on m.Contract_Start = rx.Contract_Start and m.Contract_End = rx.Contract_End

inner join #Cap2 c
on m.Contract_Start = c.Contract_Start and m.Contract_End = c.Contract_End

--Period 3
select * from #Members3
select * from #Premium3

select m.Contract_Start, m.Contract_End, (m.Med_Amt + mh.MH_Amt + rx.Rx_Amt + c.Cap_Amt) as Total

from #IP_OP3 m 

inner join #MH3 mh 
on m.Contract_Start = MH.Contract_Start and m.Contract_End = MH.Contract_End

inner join #Pharmacy3 rx
on m.Contract_Start = rx.Contract_Start and m.Contract_End = rx.Contract_End

inner join #Cap3 c
on m.Contract_Start = c.Contract_Start and m.Contract_End = c.Contract_End

#12

First, use YYYYMMDD date format over other formats when specifying literal dates. The YYYYMMDD format is interpreted correctly regardless of locale/regional settings. For example:

-- instead of this
set	@ContractStart1 = '04/01/2011'
-- use this
set	@ContractStart1 = '20110401'

But that may not be your problem. You have several where clauses that use these date variables - for example:

where		ReportingMonth between $ContractStart1 and $ContractEnd1	
			and PolicyNumber = '044MG'

Look up what the data type of ReportingMonth column is. You can find this info if you use SSMS object explorer to navigate down to the table and open up the columns node. If it is char/varchar or anything other that a date/datetime type, then the error message is telling you that some of the values in that column cannot be converted to a date/datetime. You can run a query such as this to see what the offending rows might be:

SELECT * FROM DW..Membermonths WHERE ISDATE(ReportingMonth) <> 1

You have to repeat that process until you find the offending table/rows.


#13

OK, Thank you. I have looked at all of the source fields (eg ReportingMonth) and think that they are all date data type, I will check again though.


#14

Im really not sure what the issue is.....to make it worse, I use the same dates and declare/call variables in other stored procedures that all work fine.


#15

That is not necessarily surprising because the offending rows might have been eliminated by other where clauses or join conditions in your other stored procedures.

For testing purposes, run the code in the stored procedure from an SSMS window, making sure that you use the exact same parameters. When the error happens, double click on the error, and your cursor will be placed on the statement that caused the problem.


#16

James,
Thanks very much for helping with this, I appreciate it!

Its driving me crazy, I still haven't figured it out!!


#17

The following query should show the fields and datatype that hold the date/datetime you use in your queries. Now, the fields that is NOT date or datetime, use the method of JamesK (the one with the "isdate(...)<>1"), and you should be able to find the non-date:

select o.name as tablename,c.name as columnname,t.name as columntype
  from sys.all_objects as o
       inner join sys.all_columns as c
               on c.object_id=o.object_id
       left outer join sys.types as t
               on t.system_type_id=c.system_type_id
 where (lower(o.name)='membermonths' and lower(c.name)='reportingmonth')
    or (lower(o.name)='premiumbilling') and lower(c.name)='premiummonth')
    or (lower(o.name)='claimheader') and (lower(c.name) in ('servicedatefrom','paiddate'))
    or (lower(o.name)='claimline') and lower(c.name)='servicedatefrom')
    or (lower(o.name)='mhclaimheader') and (lower(c.name) in ('servicedatefrom','paiddate'))
    or (lower(o.name)='mhclaimline') and lower(c.name)='servicedatefrom')
    or (lower(o.name)='pharmacyservice') and lower(c.name) in ('servicedatefrom','lagpaiddate'))

#18

Thanks for this. I have confirmed that all of my date fields are in fact dates (date, not null).

When you enclose a date in single quotes (eg - '04/01/2011') does that convert it to a varchar??

This issue really has me stumped because it works fine in many other places.....


#19

Depending on the locale/regional setting and the "MDY" settings, 04/01/2011 might be interpreted as April 1, 2011 or January 4, 2011, or might give you an error.

Instead if you use '20110401' to represent April 1, 2011, it will be interpreted unambiguously by SQL Server. So when writing literal dates, always use the YYYYMMDD format.


#20

Thank you James!

Is it necessary to enclose them in single quotes?