March 6, 2009 at 8:37 am
I have currently created a stored procedure that inserts data into a truncated table. However when I execute the stored procedure I get the following error:
Server: Msg 241, Level 16, State 1, Procedure sp_RIO_v_CIS, Line 11
Syntax error converting datetime from character string.
I am not sure why I am getting this error.
The script used to create the table is as follows
CREATE TABLE [CIS_vs_RIO] (
[SEQUENCE_ID] [int] NULL ,
[NHS_NUMBER] [int] NULL ,
[PRIMARY_CARE_TRUST] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[EVENT_DATE] [datetime] NULL ,
[REFFERAL_DATE] [datetime] NULL ,
[REFFERAL_SOURCE_DESCRIPTION] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[SPECIALTY] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[CLINIC] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[STAFF_FIRSTNAME] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[STAFF_SURNAME] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[PFIRSTNAME] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[PSURNAME] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[POSTCODE] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[AGE] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[GENDER] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[DATEOFBIRTH] [datetime] NULL ,
[APPOINTMENT_TYPE] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[APPOINTMENT_OUTCOME] [nvarchar] (80) COLLATE Latin1_General_CI_AS NULL ,
[GP_CODE] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[GP_FIRSTNAME] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[GP_SURNAME] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[PRACTICE_CODE] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[ETHNICITY] [nvarchar] (80) COLLATE Latin1_General_CI_AS NULL ,
[CIS/RIO] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
The code I used to create the stored procedure is as follows:
CREATE PROCEDURE sp_RIO_v_CIS @startDate DATETIME @endDate DATETIME
AS
TRUNCATE TABLE dbo.CIS_vs_RIO
--INSERT RIO DATA
INSERT INTO dbo.CIS_vs_RIO(
SEQUENCE_ID
,NHS_NUMBER
,PRIMARY_CARE_TRUST
,EVENT_DATE
,REFFERAL_DATE
,REFFERAL_SOURCE_DESCRIPTION
,SPECIALTY
,CLINIC
,STAFF_FIRSTNAME
,STAFF_SURNAME
,PFIRSTNAME
,PSURNAME
,POSTCODE
,AGE
,GENDER
,DATEOFBIRTH
,APPOINTMENT_TYPE
,APPOINTMENT_OUTCOME
,GP_CODE
,GP_FIRSTNAME
,GP_SURNAME
,PRACTICE_CODE
,ETHNICITY
,[CIS/RIO])
SELECT
AAP.SequenceID
,CL.NNN
,GPP.PCG
,AAP.AppointmentDate
,AR.ReferralDateTime
,ARS.CodeDescription
,GS.CodeDescription
,ACCT.CodeDescription
,GPS.FirstName
,GPS.Surname
,CL.Firstname
,CL.Surname
,CA.PostCode
,convert(nvarchar(6),(datediff (YEAR,CL.DateOfBirth,AAP.AppointmentDate)))
,CL.Gender
,CL.DateOfBirth
,AO.CodeDescription
,AAT.CodeDescription
,CG.GPCode
,GP.Forename
,GP.Surname
,CG.PracticeCode
,GET.CodeDescription
,'RIO'
FROM rioLive.dbo.AmsAppointment AAP
INNER JOIN rioLive.dbo.AMSAppointmentContact APC ON APC.SequenceID = AAP.SequenceID
INNER JOIN rioLive.dbo.AMSreferral AR ON AR.ClientID = APC.ClientID AND AR.ReferralNumber = APC.ReferralID --SELECT ALL APPOINTMENTS WITH REFERRALS
INNER JOIN rioLive.dbo.AMSReferralSource ARS ON AR.ReferralSource = ARS.Code
INNER JOIN rioLive.dbo.GenHCP GHP ON GHP.GenHCPCode = AAP.GenHCPCode -- GET HCP NAME
INNER JOIN rioLive.dbo.GenPerson GPS ON GHP.GenHCPCode = GPS.GenPersonID
INNER JOIN rioLive.dbo.Client CL ON CL.ClientID = APC.ClientID --MATCH CLIENTS TO GET CLIENT DETAILS
INNER JOIN rioLive.dbo.AmsAppointmentType AAT ON AAT.Code = AAP.AppointmentType--GET APPOINTMENT TYPE
INNER JOIN rioLive.dbo.ClientAddress CA ON CL.ClientID = CA.ClientID AND CA.ToDate is NULL --GET CLIENT DETAILS
INNER JOIN rioLive.dbo.ClientGP CG ON CL.ClientID = CG.ClientID AND CG.ToDate is NULL --GET GP DETAILS
INNER JOIN rioLive.dbo.GenGP GP ON CG.GPCode = GP.CODE-- GET GP description
INNER JOIN rioLive.dbo.GenGPPractice GPP ON CG.PracticeCode = GPP.Code --GET PRACTISE CODE DETAILS
INNER JOIN rioLive.dbo.AmsAppointmentContactActivity ACA ON ACA.SequenceID = AAP.SequenceID
LEFT JOIN rioLive.dbo.GenActivity GA ON GA.Code = ACA.GenActivityCode
LEFT JOIN rioLive.dbo.AmsOutcome AO ON AO.Code = APC.Outcome --GET OUTCOME
LEFT OUTER JOIN rioLive.dbo.GenCancellationReason GCR ON AAP.CancellationReason = GCR.Code
LEFT OUTER JOIN rioLive.dbo.GenCancellationReason GR ON APC.CancellationReason = GR.Code
INNER JOIN rioLive.dbo.GenSpecialty GS ON AR.SpecialtyReferredTo = GS.Code -- SPECIALITY CODE DESCRIPTION
LEFT JOIN rioLive.dbo.AmsClinicTemplate ACCT ON ACCT.ClinicID = AAP.ClinicID
LEFT JOIN rioLive.dbo.GenEthnicity GET ON CL.ETHNICITY = GET.CODE
WHERE AAP.AppointmentDate >= @startDate AND AAP.AppointmentDate <= @endDate
--INSERT CIS DATA
INSERT INTO dbo.CIS_vs_RIO(
SEQUENCE_ID
,NHS_NUMBER
,PRIMARY_CARE_TRUST
,EVENT_DATE
,REFFERAL_DATE
,REFFERAL_SOURCE_DESCRIPTION
,SPECIALTY
,CLINIC
,STAFF_FIRSTNAME
,STAFF_SURNAME
,PFIRSTNAME
,PSURNAME
,POSTCODE
,AGE
,GENDER
,DATEOFBIRTH
,APPOINTMENT_TYPE
,APPOINTMENT_OUTCOME
,GP_CODE
,GP_FIRSTNAME
,GP_SURNAME
,PRACTICE_CODE
,ETHNICITY
,[CIS/RIO])
SELECT
A.MPI
,A.NHS_NUM
,A.PCG_CODE
,CONVERT(datetime,A.EV_DATE,120)
,CONVERT(datetime,A.REF_DATE,120)
,U_SOR_L
,A.CARE_GROUP
,A.U_CONTACT_LOC_S
,ST.FNAME
,ST.SNAME
,A.FNAME
,A.SNAME
,A.U_POSTCODE
,B.AGE
,B.SEX
,B.U_DOB_DATE
,CASE WHEN A.U_INIT_SUBS = 'SUBS' THEN 'FUA' WHEN A.U_INIT_SUBS = 'INIT' THEN 'FA' END
,CASE WHEN A.DNA IS NULL THEN 'ATTENDED'WHEN A.DNA = '' THEN 'ATTENDED' ELSE A.DNA END
,A.GPCODE
,GP.FIRST_NAME
,GP.SURNAME
,A.U_PRACTICE_CODE
,A.ETH_GROUP
,'CIS'
FROM
BW1.dbo.qryIntermediate_CIS_BrentEvents A LEFT JOIN BW1.dbo.qryIntegerMPI_MASTER B
ON B.MPI_NO = A.MPI LEFT JOIN BRENTREPODB02.Olympus.dbo.GENERAL_PRACTITIONER GP
ON A.GPCODE = GP.G_CODE LEFT JOIN BW1.dbo.tblDataWarehouse_STAFF ST
ON A.STAFF_CODE = ST.[@ID]
WHERE CONVERT(datetime,A.EV_DATE,120)>= @startDate AND CONVERT(datetime,A.EV_DATE,120) <= @endDate
March 6, 2009 at 8:49 am
Mmhh... a bit complicated!
When you are facing complex problems, try to cut into pieces.
First of all, does the SELECT statement run fine? Try to run it without the INSERT part.
If it runs fine (I don't think it does), focus on the columns in the destination table defined as DATETIME and cast the columns in the SELECT statement to the appropriate type.
If it doesn't, focus on the converted columns: maybe the ISDATE() function can help you.
Regards
Gianluca
-- Gianluca Sartori
March 6, 2009 at 9:04 am
What's the data type of the following columns?
If they are character fields, how are the date strings formatted?
BW1.dbo.qryIntermediate_CIS_BrentEvents.EV_DATE
BW1.dbo.qryIntermediate_CIS_BrentEvents.REF_DATE
Also the following isn't a reliable way of calculating a person's age - it can be off by one:
convert(nvarchar(6),(datediff (YEAR,CL.DateOfBirth,AAP.AppointmentDate)))
March 6, 2009 at 9:11 am
I have carried out the inserts and outside of the stroed procedure and it works. However when I run the stored procedure I get the error message.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply