Help with SSRS

Davesm
Davesm used Ask the Experts™
on
this is the error i get when i try run the bellow script in ssrs (Conversion failed when converting date and/or time from character string.)

DECLARE @start_date char(5) DECLARE @end_Date char(5)
          
   set @start_date = convert(datetime, convert(char(5), year(dateadd(mm,-1,getdate()))) + '/'+ Right(Convert(char(5), Month(dateadd(mm,-1,getdate()))),2)+ '/01')
   set @end_date   = convert(datetime, convert(char(5), year(dateadd(mm,-0,getdate()))) + '/'+ Right(Convert(char(5), Month(dateadd(mm,-0,getdate()))),2)+ '/01')


select   a.application_id
		,ApplicationReference as NasRefNo
	,Application_ILPNo
	,at.ApplicationType_Name
	,PropertyType_Name
	,Region_Name
	,employmentstatus= (case when
                           (CASE a.applicanttype_id 
				when 1 then
				(select top 1 es.employmentstatus_name
				  from nas.dbo.applicantemployment ae with(nolock), nas.dbo.employmentstatus es with(nolock)
				  where ae.employmentstatus_id = es.employmentstatus_id
				  and ae.applicant_id = 
					(select top 1 applicant_id from nas.dbo.applicationapplicant with(nolock)
					where application_id = a.application_id))
	                    else	
				(select top 1 es.employmentstatus_name
				  from nas.dbo.applicantemployment ae with(nolock), nas.dbo.employmentstatus es with(nolock)
				  where ae.employmentstatus_id = es.employmentstatus_id
				  and ae.applicant_id =
					(select top 1 applicant_id from nas.dbo.applicantcompany with(nolock)
					where application_id = a.application_id))
	                    end) = 'Self-Employed' THEN 'Self-Employed'
                        ELSE 'Salaried'
                        END)
	,nas.dbo.fn_payout_getmeasure(	source.source_name, 
				applicationchannel.applicationchannel_name,
				coalesce(mortgageoriginator.mortgageoriginator_name,eta.estateagency_name),
				isnull(loandetails.branch_id, case when isnumeric(a.application_branchcode) = 1
				then a.application_branchcode end)) as measure
	,Missing_Docs = NULL
	,MO_EA = coalesce(MortgageOriginator_Name,EstateAgency_Name)
	,Attorney_Name
	,Attorney_TelephoneCode
	,Attorney_TelephoneNumber
	,Attorney_EMail
	,bs.Branch as NewBranchName
    ,bs.Cost_centre as NewCostCentre
    ,bs.Province as NewProvince
    ,bs.Area as NewArea
	,Applicationtype_Rollup
	,Application_Date



		,WorkHours_CAPSTART_CAPCOMP = mis.dbo.mis_Workhours((select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (2) and a1.application_id = a.application_id),
                                         (select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (3) and a1.application_id = a.application_id)) 
                                         
       ,WorkHours_CAPCOMP_VALCOMP = mis.dbo.mis_Workhours((select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (3) and a1.application_id = a.application_id),
                                         (select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (17) and a1.application_id = a.application_id)) 
                                         
       ,WorkHours_VALCOMP_AIP = mis.dbo.mis_Workhours((select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (17) and a1.application_id = a.application_id),
                                         (select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (24,26,124) and a1.application_id = a.application_id))
     
      ,WorkHours_AIP_VALREQ = mis.dbo.mis_Workhours((select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (24,26,124) and a1.application_id = a.application_id),
                                         (select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (36) and a1.application_id = a.application_id))                                 
                                         
       ,WorkHours_VALREQ_VALRECD = mis.dbo.mis_Workhours((select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (36) and a1.application_id = a.application_id),
                                         (select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (37) and a1.application_id = a.application_id))
        
        ,WorkHours_VALRECD_FINDECS = mis.dbo.mis_Workhours((select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (37) and a1.application_id = a.application_id),
                                         (select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (47,48,117,125) and a1.application_id = a.application_id)) 
                                         
       ,WorkHours_FINDESC_CUSTACCFINDECS = mis.dbo.mis_Workhours((select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (47,48,117,125) and a1.application_id = a.application_id),
                                         (select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (127) and a1.application_id = a.application_id))
                                         
       ,WorkHours_CUSTACCFINDECS_ElECTINST = mis.dbo.mis_Workhours((select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (127) and a1.application_id = a.application_id),
                                         (select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (301,54) and a1.application_id = a.application_id))                                  
                                                               
       ,WorkHours_ELECINST_BONDREG = mis.dbo.mis_Workhours((select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (301,54) and a1.application_id = a.application_id),
                                         (select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (114,316) and a1.application_id = a.application_id))
       
       ,WorkHours_BONDREG_PAYOUT = mis.dbo.mis_Workhours((select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (114,316) and a1.application_id = a.application_id),
                                         (select min(Payout_EntryDate) 
                                         from nas..Payout p with(nolock) 
                                         where p.application_id = a.application_id))
  
from nas..Application a

	left join nas_Frontier..Step_Running_Singularity srs on srs.ApplicationReferenceID = a.Application_ID
	left join nas..applicationType at on at.applicationType_ID = a.applicationType_ID
	left join mis..MIS_ApplicationRegion AR on AR.Application_ID = a.Application_ID
	left join nas..source source on source.source_ID = a.source_ID
	left join nas..applicationChannel applicationChannel on applicationChannel.applicationChannel_ID = a.applicationChannel_ID
	left join nas..homeloan homeloan on homeloan.Homeloan_ID = a.Application_ID
	left join nas..MortgageOriginator MortgageOriginator on MortgageOriginator.MortgageOriginator_ID = homeloan.MortgageOriginator_ID
	left join nas..loandetails loandetails on loandetails.loandetails_ID = a.loandetails_ID
	left join nas..applicationAttorney applicationAttorney on applicationAttorney.Application_ID = a.Application_ID
	left join nas..Attorney Attorney with (nolock) on attorney.attorney_ID = applicationAttorney.attorney_ID
	left join nas..estateagent ea with (nolock) on ea.estateagent_id = homeloan.estateagent_id
	left join (select max(estateagency_id) as estateagency_id,
					estateagent_id
				from nas..agencyestateagent agencyestateagent with (nolock)
				group by estateagent_id) as agencyestateagentmax on agencyestateagentmax.estateagent_id = ea.estateagent_id
	left join nas..estateagency eta with (nolock) on eta.estateagency_id = agencyestateagentmax.estateagency_id
	left join  nas.dbo.branch branch with(nolock) on branch.branch_id = loandetails.branch_id
    left join  nas.dbo.branchbanking_branch branchbanking with (nolock) on branchbanking.branchcode = substring(a.application_branchcode, 3, 3)
    left join (select Cost_centre, Branch, Province, Area
               from mis..BranchStructure_April2009 BranchStructure_April2009) bs on bs.cost_centre = ltrim(convert(bigint,mis.dbo.mis_branchcode(a.source_id,a.application_branchcode)))
	left join nas..ApplicationCAV cav on a.application_id=cav.application_id
	left join mis..MIS_ApplicationtypeRollup R with(nolock) on r.applicationtype_id=a.applicationtype_id
	left join nas..Property Property on Property.Property_ID = a.Property_ID
	left join nas..PropertyType PropertyType on PropertyType.PropertyType_ID = Property.PropertyType_ID


--where (a.Application_Date  BETWEEN @start_date AND @end_date )

where a.Application_ID in ( select Application_ID 
							from Nas..ApplicationStatusChange
							where ApplicationStatusChange_Date BETWEEN @start_date AND @end_date )

Open in new window


How can I fix it
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Aaron ShiloChief Database Architect
Commented:
what is the datatype for the "ApplicationStatusChange_Date " column the "Nas..ApplicationStatusChange" table ?

Author

Commented:
hi its a (datetime, not null)

I also dont understand what set start_Date and End_date are trying to  do
Chief Database Architect
Commented:
the problem

where a.Application_ID in ( select Application_ID 
							from Nas..ApplicationStatusChange
							where ApplicationStatusChange_Date BETWEEN @start_date AND @end_date )

Open in new window


the reason

this is becouse the variables @start_date and @end_date are char(5)
and the ApplicationStatusChange_Date is datetime .

this will never work unless you convert the ApplicationStatusChange_Date column
convert(datetime, convert(char(5), year(dateadd(mm,-1ApplicationStatusChange_Date))) + '/'+ Right(Convert(char(5), Month(dateadd(mm,-1,ApplicationStatusChange_Date))),2)+ '/01')

Open in new window


so you will result in

DECLARE @start_date char(5) DECLARE @end_Date char(5)
          
   set @start_date = convert(datetime, convert(char(5), year(dateadd(mm,-1,getdate()))) + '/'+ Right(Convert(char(5), Month(dateadd(mm,-1,getdate()))),2)+ '/01')
   set @end_date   = convert(datetime, convert(char(5), year(dateadd(mm,-0,getdate()))) + '/'+ Right(Convert(char(5), Month(dateadd(mm,-0,getdate()))),2)+ '/01')


select   a.application_id
		,ApplicationReference as NasRefNo
	,Application_ILPNo
	,at.ApplicationType_Name
	,PropertyType_Name
	,Region_Name
	,employmentstatus= (case when
                           (CASE a.applicanttype_id 
				when 1 then
				(select top 1 es.employmentstatus_name
				  from nas.dbo.applicantemployment ae with(nolock), nas.dbo.employmentstatus es with(nolock)
				  where ae.employmentstatus_id = es.employmentstatus_id
				  and ae.applicant_id = 
					(select top 1 applicant_id from nas.dbo.applicationapplicant with(nolock)
					where application_id = a.application_id))
	                    else	
				(select top 1 es.employmentstatus_name
				  from nas.dbo.applicantemployment ae with(nolock), nas.dbo.employmentstatus es with(nolock)
				  where ae.employmentstatus_id = es.employmentstatus_id
				  and ae.applicant_id =
					(select top 1 applicant_id from nas.dbo.applicantcompany with(nolock)
					where application_id = a.application_id))
	                    end) = 'Self-Employed' THEN 'Self-Employed'
                        ELSE 'Salaried'
                        END)
	,nas.dbo.fn_payout_getmeasure(	source.source_name, 
				applicationchannel.applicationchannel_name,
				coalesce(mortgageoriginator.mortgageoriginator_name,eta.estateagency_name),
				isnull(loandetails.branch_id, case when isnumeric(a.application_branchcode) = 1
				then a.application_branchcode end)) as measure
	,Missing_Docs = NULL
	,MO_EA = coalesce(MortgageOriginator_Name,EstateAgency_Name)
	,Attorney_Name
	,Attorney_TelephoneCode
	,Attorney_TelephoneNumber
	,Attorney_EMail
	,bs.Branch as NewBranchName
    ,bs.Cost_centre as NewCostCentre
    ,bs.Province as NewProvince
    ,bs.Area as NewArea
	,Applicationtype_Rollup
	,Application_Date



		,WorkHours_CAPSTART_CAPCOMP = mis.dbo.mis_Workhours((select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (2) and a1.application_id = a.application_id),
                                         (select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (3) and a1.application_id = a.application_id)) 
                                         
       ,WorkHours_CAPCOMP_VALCOMP = mis.dbo.mis_Workhours((select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (3) and a1.application_id = a.application_id),
                                         (select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (17) and a1.application_id = a.application_id)) 
                                         
       ,WorkHours_VALCOMP_AIP = mis.dbo.mis_Workhours((select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (17) and a1.application_id = a.application_id),
                                         (select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (24,26,124) and a1.application_id = a.application_id))
     
      ,WorkHours_AIP_VALREQ = mis.dbo.mis_Workhours((select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (24,26,124) and a1.application_id = a.application_id),
                                         (select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (36) and a1.application_id = a.application_id))                                 
                                         
       ,WorkHours_VALREQ_VALRECD = mis.dbo.mis_Workhours((select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (36) and a1.application_id = a.application_id),
                                         (select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (37) and a1.application_id = a.application_id))
        
        ,WorkHours_VALRECD_FINDECS = mis.dbo.mis_Workhours((select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (37) and a1.application_id = a.application_id),
                                         (select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (47,48,117,125) and a1.application_id = a.application_id)) 
                                         
       ,WorkHours_FINDESC_CUSTACCFINDECS = mis.dbo.mis_Workhours((select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (47,48,117,125) and a1.application_id = a.application_id),
                                         (select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (127) and a1.application_id = a.application_id))
                                         
       ,WorkHours_CUSTACCFINDECS_ElECTINST = mis.dbo.mis_Workhours((select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (127) and a1.application_id = a.application_id),
                                         (select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (301,54) and a1.application_id = a.application_id))                                  
                                                               
       ,WorkHours_ELECINST_BONDREG = mis.dbo.mis_Workhours((select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (301,54) and a1.application_id = a.application_id),
                                         (select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (114,316) and a1.application_id = a.application_id))
       
       ,WorkHours_BONDREG_PAYOUT = mis.dbo.mis_Workhours((select min(a1.ApplicationstatusChange_Date) 
                                         from nas..ApplicationstatusChange a1 with(nolock) 
                                         where a1.ApplicationStatusChange_New_Status in (114,316) and a1.application_id = a.application_id),
                                         (select min(Payout_EntryDate) 
                                         from nas..Payout p with(nolock) 
                                         where p.application_id = a.application_id))
  
from nas..Application a

	left join nas_Frontier..Step_Running_Singularity srs on srs.ApplicationReferenceID = a.Application_ID
	left join nas..applicationType at on at.applicationType_ID = a.applicationType_ID
	left join mis..MIS_ApplicationRegion AR on AR.Application_ID = a.Application_ID
	left join nas..source source on source.source_ID = a.source_ID
	left join nas..applicationChannel applicationChannel on applicationChannel.applicationChannel_ID = a.applicationChannel_ID
	left join nas..homeloan homeloan on homeloan.Homeloan_ID = a.Application_ID
	left join nas..MortgageOriginator MortgageOriginator on MortgageOriginator.MortgageOriginator_ID = homeloan.MortgageOriginator_ID
	left join nas..loandetails loandetails on loandetails.loandetails_ID = a.loandetails_ID
	left join nas..applicationAttorney applicationAttorney on applicationAttorney.Application_ID = a.Application_ID
	left join nas..Attorney Attorney with (nolock) on attorney.attorney_ID = applicationAttorney.attorney_ID
	left join nas..estateagent ea with (nolock) on ea.estateagent_id = homeloan.estateagent_id
	left join (select max(estateagency_id) as estateagency_id,
					estateagent_id
				from nas..agencyestateagent agencyestateagent with (nolock)
				group by estateagent_id) as agencyestateagentmax on agencyestateagentmax.estateagent_id = ea.estateagent_id
	left join nas..estateagency eta with (nolock) on eta.estateagency_id = agencyestateagentmax.estateagency_id
	left join  nas.dbo.branch branch with(nolock) on branch.branch_id = loandetails.branch_id
    left join  nas.dbo.branchbanking_branch branchbanking with (nolock) on branchbanking.branchcode = substring(a.application_branchcode, 3, 3)
    left join (select Cost_centre, Branch, Province, Area
               from mis..BranchStructure_April2009 BranchStructure_April2009) bs on bs.cost_centre = ltrim(convert(bigint,mis.dbo.mis_branchcode(a.source_id,a.application_branchcode)))
	left join nas..ApplicationCAV cav on a.application_id=cav.application_id
	left join mis..MIS_ApplicationtypeRollup R with(nolock) on r.applicationtype_id=a.applicationtype_id
	left join nas..Property Property on Property.Property_ID = a.Property_ID
	left join nas..PropertyType PropertyType on PropertyType.PropertyType_ID = Property.PropertyType_ID


--where (a.Application_Date  BETWEEN @start_date AND @end_date )

where a.Application_ID in ( select Application_ID 
							from Nas..ApplicationStatusChange
							where convert(datetime, convert(char(5), year(dateadd(mm,-1ApplicationStatusChange_Date))) + '/'+ Right(Convert(char(5), Month(dateadd(mm,-1,ApplicationStatusChange_Date))),2)+ '/01')
 BETWEEN @start_date AND @end_date )

Open in new window


Author

Commented:
hi when i try put this script into SSRS i get the following error (An Error occured while the query design method was being saved. Incorrect syntax near 'applicationstatuschange_date'

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial