asked
Conversion failed when converting datetime from character string error
I'm getting that error for this statement. I'm guessing that it is the year or hardship entries being that they were recently added. Any thoughts? Syntax issues? SELECT [ID]=
n.ID , [Apply]=CAST(0 AS bit) , [Full Name]=n.FULL_NAME , [Member Type]=n.MEMBER_TYPE , [Category]=n.CATEGORY , [City]=n.CITY , [State]=n.STATE_PROVINCE , [SUBTYPE_PROMPT]=CAST(''N'' AS varchar(31)) , Year(ap.medical_school) , ap.hardship FROM dbo.Name n JOIN AAPMR_Profile ap on
n.id =
ap.id LEFT JOIN Activity a ON
a.ID=
n.ID AND a.ACTIVITY_TYPE=''Drop'' AND a.UF_3<='' '' WHERE n.MEMBER_TYPE IN (''AF'',''AM'',''CM'',''FM'',''PHD'',''PTF'') AND n.[STATUS]=''A'' AND Year(ap.Hardship) < year(getdate()) ORDER by
n.id ' ELSE IF ISNULL(@ACTIVITY_TYPE,'')='NEW' SELECT @SQL=N' SELECT [ID]=
n.ID , [Apply]=CAST(0 AS bit) --, [Last, First]=n.LAST_FIRST , [Full Name]=n.FULL_NAME , [Member Type]=n.MEMBER_TYPE , [Category]=n.CATEGORY --, [Status]=n.[STATUS] , [City]=n.CITY , [State]=n.STATE_PROVINCE , [SUBTYPE_PROMPT]=CAST('''' AS varchar(31)) , [UF_1_PROMPT]=CAST('''' AS varchar(255)) , [OTHER_CODE_PROMPT]=CAST('''' AS varchar(30)) , [Dues Pay Date]=ISNULL((SELECT CONVERT(varchar(10),MAX(t.TRANSACTION_DATE),101) FROM Activity t WHERE t.ACTIVITY_TYPE=''DUES'' AND YEAR(t.TRANSACTION_DATE)=YEAR(GETDATE()) AND
t.ID=
n.ID),'''') FROM dbo.Name n LEFT JOIN Activity a ON
a.ID=
n.ID AND a.ACTIVITY_TYPE=@ACTIVITY_TYPE AND a.UF_3<='' '' WHERE n.MEMBER_TYPE IN (''P3'',''P4'',''P5'',''P8'',''P9'') AND n.[STATUS]=''A'' AND n.CATEGORY=''PM'' AND a.SEQN IS NULL ORDER BY n.LAST_FIRST ',
Hard to tell with the query. But if you have doubt on some columns then verify them by using the ISDATE function. Moreover, you can also debug your dynamic query by printing it before execution. For e.g. select * FROM AAPMR_Profile ap WHERE ISDATE(ap.medical_school) = 0 OR ISDATE(ap.hardship) = 0
@aross BTW, are you sure "ap.medical_school" is a date column. I think this could be the problem ;)
Have you executed the suggested query and found any culprit rows? If yes, then correct them ;)
ap.medical has vales like 06-21-2009 in it. i did year(ap.medical_school) to grab the year. In the select statement can I do something like CAST(year(ap.Medical_school)AS datetime)? In the where clause CAST(CONVERT(varchar(10),ISNULL(year(ap.Medical_school),'19000101'),101) AS datetime) = year(getdate()) ?
it's better. I got the query to execute but now it doesn't like the null values. There are only 100 or so entries in the database that contain values. 34000 do not.
Is there any other way to code that line besides using substring? Having null values is a problem.
8 People are following this question.
