Error converting data type nvarchar to date.

I am getting the above error when trying to add a dataset to my report. Here is my stored procedure below. Can anybody help?

USE [HSIU_TEST]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [ssrs].[spKEMHWaitTimeCaseList]
	-- Add the parameters for the stored procedure here
		@LastDay DATE

AS
BEGIN
-- SET NOCOUNT ON TO PREVENT EXTRA RESULT SETS FROM INTERFERING WITH SELECT STATEMENTS
SET NOCOUNT ON;

-- CHECK FOR PERMISSIONS TO ACCESS THE DATA IN CASE THE PROCEDURE WAS EXECUTED INSIDE SQL
DECLARE @count int
SELECT @count = COUNT(*) from [ssrs].[vwHospitals] where HOSP_CODE = 'KEMH'
	
IF @count = 0
BEGIN
	RAISERROR 
	(50001, -- ITEM EXISTS
	16, -- SEVERITY
	1 -- STATE, CAN BE FROM 1 TO 127 AND CAN BE USED FOR LABELING THE LOCATIONS IN CODE.
	);
END


DECLARE @HOSP_CODE VARCHAR(4)
DECLARE @FirstDay DATE
--DECLARE @LastDay DATE

-- Set Hospital for report
SET @HOSP_CODE = 'hju'

-- Set dates for report period
--SET @LastDay = CONVERT(DATE,GETDATE())
SET @FirstDay = DATEADD(day,-60, @LastDay)


SELECT

PT_CODE,
[OPE_ORDER_IN_SESS_ASC] AS POSITION,
CONVERT(DATE,ADM_DATE_TIME) AS [DATE],
CONVERT(TIME,ADM_DATE_TIME) ADM_TIME,
CONVERT(TIME,OPE_START_TIME) AS KNIFE_TO_SKIN,
DATEDIFF(MINUTE,ADM_DATE_TIME,OPE_START_TIME) AS WAITING_MINS

INTO #DATA

FROM kemh.vwTMSProcedures AS A 
LEFT JOIN vwICD10ProcedureCodes AS B ON A.OPP_PRO_ICD9 = B.PROC_CODE
LEFT JOIN DBO.vwTMSOperationsAndSchedule AS C ON A.OPP_ACCOUNT_NO = C.OPE_ACCOUNT_NO AND A.OPP_OPE_KEY = C.OPE_KEY AND C.HOSP_CODE = 'KEMH'
LEFT JOIN kemh.vwTOPASInpatients AS D ON A.OPP_ACCOUNT_NO = D.ACCT_NO

WHERE

OPE_PLAN_START >= @FirstDay and OPE_PLAN_START <= @LastDay and
(A.OPP_PRO_STATUS <> 'CA') and 
(A.OPP_PRO_STRT IS NOT NULL) and 
(A.OPP_PRO_END IS NOT NULL) and 
A.OPP_PRO_ICD9 in ('16520-02','16520-00') and
OPP_PRO_STATUS = 'RQ' AND
SESS_URGENCY = 'ELECT' AND
DATENAME(WEEKDAY,OPP_PRO_STRT) IN ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday') AND
CONVERT(DATE,OPE_START_TIME) = CONVERT(DATE,ADM_DATE_TIME)
                        
order by OPP_PRO_STRT, OPP_ACCOUNT_NO


SELECT
POSITION,
AVG(WAITING_MINS) AS Average
SSRS.MEDIAN(WAITING_MINS) AS Median
FROM #DATA
GROUP BY POSITION
ORDER BY POSITION

DROP TABLE #DATA

END
GO

GRANT EXECUTE ON [ssrs].[jjj] TO hjj_reporting
GO

April 28th, 2015 11:00pm

HI,

You able to run the Stored Procedure in Sql Server.

if yes then try to write query in query type: Text as

Execute [ssrs].[spKEMHWaitTimeCaseList] @LastDay

Thanks

Prasad

Free Windows Admin Tool Kit Click here and download it now
April 29th, 2015 12:03am

Check the places where you're inserting/converting value to date field like ADM_DATE_TIME etc. Make sure the values in the field are valid date values and in proper consistent format. If there are spurios values either filter them out or convert them to a valid default value

In any case the best format for passing date values is ISO format as its unambiguos and universally accepted

http://visakhm.blogspot.com/2011/12/why-iso-format-is-recommended-while.ht

April 29th, 2015 1:14am

Looks like you have defined a datatype nvarchar for ADM_DATE_TIME column , Can you run the below to examine that column 

SELECTADM_DATE_TIME,

ISDATE(ADM_DATE_TIME) from tbl

Does it return 0 for some records?

Free Windows Admin Tool Kit Click here and download it now
April 29th, 2015 1:24am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics