select   SID AS SID_Reg,
         ISNULL(CAST(SDATE AS DATETIME),NULL) AS SDATE_Reg,
         SSTATE AS SSTATE_Reg, 
         St_status AS St_status_Reg,
  (select B.Stud_FSCL_YR from stud_fiscyear B,Stu_data A WHERE    B.Stud_FSCL_YR BETWEEN 2005 AND 2011  
				and ISNULL(CAST(A.SDATE AS DATETIME),NULL)  IS NOT NULL
				and ISNULL(CAST(A.SDATE AS DATETIME),NULL)  BETWEEN B.STBGN_DT AND B.STEND_DT )AS ST_FSCL_YR, 
  (select B.STU_FSCLVL from stud_fiscyear B,Stu_data A
					  WHERE b.Stud_FSCL_YR BETWEEN 2008 AND 2011  
					  and ISNULL(CAST(A.SDATE AS DATETIME),NULL)  IS NOT NULL
					  and ISNULL(CAST(A.SDATE AS DATETIME),NULL)  BETWEEN B.STBGN_DT AND B.STEND_DT )AS ST_FSCL_KVAL,
  Rank() OVER (Partition by SID,SSTATE_Reg Order by ISNULL(CAST(SDATE AS DATETIME),NULL) asc) as 'Rank'
  from Stu_data where   St_status = 'Registered'

quote:

---

Originally posted by sqlfresher2k7

I got the below error for the query

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

Please help..

select   SID AS SID_Reg,
         ISNULL(CAST(SDATE AS DATETIME),NULL) AS SDATE_Reg,
         SSTATE AS SSTATE_Reg, 
         St_status AS St_status_Reg,
  (select B.Stud_FSCL_YR from stud_fiscyear B,Stu_data A WHERE    B.Stud_FSCL_YR BETWEEN 2005 AND 2011  
				and ISNULL(CAST(A.SDATE AS DATETIME),NULL)  IS NOT NULL
				and ISNULL(CAST(A.SDATE AS DATETIME),NULL)  BETWEEN B.STBGN_DT AND B.STEND_DT )AS ST_FSCL_YR, 
  (select B.STU_FSCLVL from stud_fiscyear B,Stu_data A
					  WHERE b.Stud_FSCL_YR BETWEEN 2008 AND 2011  
					  and ISNULL(CAST(A.SDATE AS DATETIME),NULL)  IS NOT NULL
					  and ISNULL(CAST(A.SDATE AS DATETIME),NULL)  BETWEEN B.STBGN_DT AND B.STEND_DT )AS ST_FSCL_KVAL,
  Rank() OVER (Partition by SID,SSTATE_Reg Order by ISNULL(CAST(SDATE AS DATETIME),NULL) asc) as 'Rank'
  from Stu_data where   St_status = 'Registered'

---