Solved

Stored Procedure Error SQL 2005

Posted on 2011-03-24
3
226 Views
Last Modified: 2012-05-11
I receive the following error when trying to execute my Stored Procedure.

Error converting data type nvarchar to datetime.

The field in my Table is a datetime. I have placed the query below as well as the code that I am using to execute the query is at the very bottom.
USE [EXPORTHpixPROD]
GO
/****** Object:  StoredProcedure [dbo].[spGetMonthlyEvaluationByEventID]    Script Date: 03/24/2011 07:25:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Michael Burk
-- Create date: 03232011
-- Description:	Report that selects Evaluations by EventID and Month
-- =============================================
ALTER PROCEDURE [dbo].[spGetMonthlyEvaluationByEventID] 
	-- Add the parameters for the stored procedure here
	(@EventID AS BIGINT,
	 @FromDate as DATETIME,
	 @ToDate as DATETIME)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT DISTINCT    
						CME_Parsed_Survey_XML.SurveyID
					   ,CME_Parsed_Survey_Results_XML.SurveyID
					   ,CME_Parsed_Survey_XML.SurveyDate
					   ,CME_Parsed_Survey_XML.EventID
					   ,CME_Parsed_Survey_XML.LicenseNumber
					   ,CME_Parsed_Survey_Results_XML.QuestionID
					   ,CME_Parsed_Survey_Results_XML.QuestionText
					   ,CME_Parsed_Survey_Results_XML.AnswerTextID
					   ,CME_Parsed_Survey_Results_XML.AnswerText
					   ,CME_Parsed_Survey_Results_XML.AnswerText2
					   ,CME_Parsed_Registration_XML.Designation
					   ,CME_Parsed_Registration_XML.FirstName
					   ,CME_Parsed_Registration_XML.LastName
					   ,CME_Parsed_Registration_XML.Title AS Suffix
					   ,CME_Courses.Title AS Title
					   ,CME_Courses.URL					  
FROM        CME_Parsed_Survey_XML INNER JOIN
			CME_Parsed_Survey_Results_XML ON CME_Parsed_Survey_XML.SurveyID = CME_Parsed_Survey_Results_XML.SurveyID
			INNER JOIN
			CME_Parsed_Registration_XML ON CME_Parsed_Survey_XML.LicenseNumber = CME_Parsed_Registration_XML.MedicalLicenseNumber
			INNER JOIN
			CME_Courses ON CME_Parsed_Survey_XML.EventID = CME_Courses.EventID
WHERE SurveyDate between (@FromDate) and (@ToDate)
END
------------------------------------------------------------------------------------------------
Code to execute query
------------------------------------------------------------------------------------------------
USE [EXPORTHpixPROD]
GO

DECLARE	@return_value int

EXEC	@return_value = [dbo].[spGetMonthlyEvaluationByEventID]
		@EventID = 87895,
		@FromDate = N'02012011',
		@ToDate = N'02312011'

SELECT	'Return Value' = @return_value

GO

Open in new window

0
Comment
Question by:mburk1968
3 Comments
 
LVL 19

Accepted Solution

by:
Rikin Shah earned 250 total points
ID: 35205955
you need to convert these dates to DateTime while executing


                @FromDate = N'02012011',
                @ToDate = N'02312011'
0
 

Author Comment

by:mburk1968
ID: 35206026
Do you have an example?
0
 
LVL 1

Expert Comment

by:ltmnm
ID: 35207415
Try changing the format of the dates that you are passing in.
from 02012011 to 02/01/2011
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Upgrading SQL 2005 Express to 2008 R2 Express 31 154
Replace Dates in query 14 55
Find results from sql within a time span 11 55
tempdb log keep growing 7 43
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question