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??
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?
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.
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.
That is the issue James, Im declaring several variables in the code......
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?
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.
yes, Im declaring it as a variable at the top of the code then calling it in the where clause
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.
ok, Ill do that now
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_EndFirst, 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.
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.
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.
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.
James,
Thanks very much for helping with this, I appreciate it!
Its driving me crazy, I still haven't figured it out!!
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'))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.....
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.
Thank you James!
Is it necessary to enclose them in single quotes?