Conversion failed when converting date and/or time from character string.

Question

Hi,

This is my code and I don't know why this error keeps coming out : PS : I did cursor to execute query.Th error showed is bold:

DECLARE RegCreatedDate CURSOR FOR
SELECT DISTINCT (CONVERT(NVARCHAR,CreatedDate,103)) 
FROM CA_Registration WHERE Month(CreatedDate)= @paMonthIn AND YEAR(CreatedDate)=@paYearIn

OPEN RegCreatedDate
FETCH NEXT FROM RegCreatedDate INTO @RegCreatedDate
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #TempDiagnosisTable


 Select    @CompanyName AS CompanyName,@RegistrationNumber AS RegistrationNo,@ParentCompanyName AS ParentCompanyName,
   @MOHFacilityCode AS MOHFacilityCode,@CompanyState as DisState,DateName( month , DateAdd( month ,CAST(@paMonthIn AS INT) , -1 )) AS DisMonth,@paYearIn as DisYear,
   dbo.CA_Registration.ID as ID, dbo.CA_Registration.MRN as MRN, dbo.CA_Registration.VisitNumber as VisitNumber,
dbo.CA_Patient.DOB as DOB, DATEDIFF(yy,CA_Patient.DOB,GETDATE()) as Age, 
dbo.CA_GenderMaster.Description as Sex, dbo.CA_DoctorMaster.DoctorName as PrimaryDoctor, 
dbo.CA_Registration.RegDate as RegistrationDate, 
                dbo.CA_DischargeDetails.DischargeDate as DODischarge, DATEDIFF(dd,ca_registration.regdate,CA_DischargeDetails.DischargeDate) as DaysStayed, case when CA_DischargeDetails.DischargeReasonID =7 then 'YES' else 'NO' END as DischargeTypeAsDeath,
                dbo.CA_PnICD.Diagnosis as Diagnosis, dbo.CA_PnICD.ICDPrimaryID as ICDPrimary, dbo.CA_PnICD.ICDDeathID as ICDDeath, dbo.CA_PnICD.ICDSecondaryID as ICDSecondary
 
 FROM           dbo.CA_Registration LEFT OUTER JOIN
     dbo.CA_PnICD ON dbo.CA_Registration.ID = dbo.CA_PnICD.ID LEFT OUTER JOIN
                      dbo.CA_DoctorMaster ON dbo.CA_Registration.PrimaryDoctorID = dbo.CA_DoctorMaster.ID LEFT OUTER JOIN
                      dbo.CA_DischargeDetails ON dbo.CA_Registration.MRN = dbo.CA_DischargeDetails.MRN AND 
                      dbo.CA_Registration.VisitNumber = dbo.CA_DischargeDetails.VisitNumber RIGHT OUTER JOIN
                      dbo.CA_Patient LEFT OUTER JOIN
                      dbo.CA_GenderMaster ON dbo.CA_Patient.GenderID = dbo.CA_GenderMaster.ID ON dbo.CA_Registration.MRN = dbo.CA_Patient.MRN 
                        where CA_Registration.ChargeTypeID in (1,2) and MONTH(ca_registration.regdate)=@paMonthIn and YEAR(ca_registration.regdate)=@paYearIn
AND dbo.CA_Registration.ID = 
(SELECT MAX(ID) FROM CA_Registration WHERE MRN = dbo.CA_Registration.MRN 
AND (CONVERT(NVARCHAR,CreatedDate,103)) = @RegCreatedDate)


FETCH NEXT FROM RegCreatedDate INTO @RegCreatedDate

END
CLOSE RegCreatedDate
DEALLOCATE RegCreatedDate


--RUN THE TABLE---

SELECT * FROM #TempDiagnosisTable ORDER BY ID

END

Answer 1

How have you designed #TempDiagnosisTable, can you show us the structure of this table ?

---

Good Luck!
Please Mark This As Answer if it solved your issue.
Please Vote This As Helpful if it helps to solve your issue

Answer 2

When i run it shows 

Msg 241, Level 16, State 1, Procedure usp_Rpt_DiagnosisStatisticalReport2, Line 112
Conversion failed when converting date and/or time from character string.

This is how i create the temporary table:

ALTER PROCEDURE [dbo].[usp_Rpt_DiagnosisStatisticalReport2]
(
 @paMonthIn NVARCHAR(30) = '', 
      @paYearIn NVARCHAR(30) = '',
 @paUserIdIn INT=0
)
AS
BEGIN

SET NOCOUNT ON;

DECLARE @LoggedInUserName AS NVARCHAR(200)
DECLARE @RegCreatedDate NVARCHAR(30) 
SET @LoggedInUserName=(SELECT UserName FROM CA_Users WHERE ID=@paUserIdIn) 

--CREATE TEMPORARY TABLE--

CREATE TABLE #TempDiagnosisTable

( CompanyName NVARCHAR(300)
,RegistrationNo NVARCHAR(50)
,ParentCompanyName NVARCHAR(300)
,MOHFacilityCode NVARCHAR(20)
,DisState               NVARCHAR(50)
,DisMonth DATETIME
,DisYear DATETIME
,ID INT
,MRN NVARCHAR(15)
,VisitNumber NVARCHAR(20)
,DOB DATETIME
,Age INT
,Sex NVARCHAR(50)
,PrimaryDoctor NVARCHAR(50)
,RegistrationDate DATETIME
,DODischarge DATETIME
,DaysStayed INT
,DischargeTypeAsDeath NVARCHAR(50)
,Diagnosis NVARCHAR(max)
,ICDPrimary SMALLINT
,ICDDeath SMALLINT
,ICDSecondary SMALLINT

)


   DECLARE @CompanyName NVARCHAR(300)
   DECLARE @RegistrationNumber NVARCHAR(50)
   DECLARE @ParentCompanyName NVARCHAR(300)
   DECLARE @MOHFacilityCode NVARCHAR(20)
   DECLARE @CompanyState NVARCHAR(50)

Answer 3

I am Suspecting one of the value for CreatedDate column is not valid, data sanity issue

Can you issue this statement and post back the results to see if there is any value which is datetime type ?

SELECT * FROM CA_Registration WHERE ISDATE ( CreatedDate ) = 0

---

Good Luck!
Please Mark This As Answer if it solved your issue.
Please Vote This As Helpful if it helps to solve your issue

Answer 4

I wrote the above query in a new query and its working fine but no results. So where am I supposed to go and check for the error?

Answer 5

We need to know at which point this code is failing so I suggest instead of inserting the record into the temp table can do some debugging using print or select and display the value of createddate with and without connversion

---

Good Luck!
Please Mark This As Answer if it solved your issue.
Please Vote This As Helpful if it helps to solve your issue

Answer 6

Are you trying to convert a string to date/time?  If so then this could be an issue with the value of the string.

Check the string value(s) to ensure that they can be recognised as a string according to the collation of the database.  e.g. if your database collation is british English then the string formula needs to be like dd/mm/yyyy and not mm/dd/yyyy (American English).  so 20/12/2014 would be acceptable whereas 12/20/2014 would not.

---

Please click "Mark As Answer" if my post helped. Tony C.

Answer 7

What about other date columns which are used in your queries (ca_registration.regdate):

SELECT * FROM CA_Registration WHERE ISDATE ( RegDate) = 0

The datetime conversion error will come only when datetime value you are trying to interpret is wrong either because of data sanity or wrong conversion format

---

Good Luck!
Please Mark This As Answer if it solved your issue.
Please Vote This As Helpful if it helps to solve your issue

Answer 8

Good day,

This post can give the full information regarding the issue. basically the issue is in "displaying format" and the implicit CONVERT operation that was done and the solution is to use explicit CONVERT with the proper style.

Date displaying format, vs Date storing format:
http://ariely.info/Blog/tabid/83/EntryId/161/Date-displaying-format-vs-Date-storing-format.aspx

Here are some of the Conclusions and Summarize from the article:

Implicit conversion of ambiguous date formats are interpreted according to the language of the connection. Always keep and following rules, in order to make your work more compatible.

v  When you specify dates in DML queries, always use constants way that are interpreted the same way for all language settings!

v  If the format that SQL Server use for the convert is wrong, then we might get the error “datetime data type resulted in an out-of-range value”. For example if the SQL Server tried to convert a number that more than 12 to month or number that is more than 31 to days.

v  Remember that those are only display formats. In the database the data is store in the same way, no matter what is your language!

---

Ronen Ariely  [Personal Site]    [Blog]    [Facebook]