sql server 2008 - My Sql Code Get An Error Of Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value '8:45 AM' to data type int -
msg 245, level 16, state 1, line 1 conversion failed when converting varchar value '8:45 am' data type int. script
declare @specimenid bigint = 0, @script varchar(max); declare @starttime int = (select convert(int,substring(start_time,0,charindex(':', start_time)) - 12) appt appt._id=112601) --select @starttime set @script = 'select distinct ptlast_name + '', '' + pt.first_name patient_name, dbo.formatdate(pt.dob,''mm/dd/yyyy'') dob, (convert(varchar, (datediff(yy, dob, appt_date) - case when (month(dob) > month(appt_date)) or (month(dob) = month(appt_date) , day(dob) > day(appt_date)) 1 else 0 end)) + ''/'' + convert(varchar, (datediff(m, dateadd(yy, (datediff(yy, dob, appt_date) - case when (month(dob) > month(appt_date)) or (month(dob) = month(appt_date) , day(dob) > day(appt_date)) 1 else 0 end), dob), appt_date) - case when day(dob) > day(appt_date) 1 else 0 end)) + ''/'' + convert(varchar, (datediff(d, (dateadd(m, (datediff(m, dateadd(yy, (datediff(yy, dob, appt_date) - case when (month(dob) > month(appt_date)) or (month(dob) = month(appt_date) , day(dob) > day(appt_date)) 1 else 0 end), dob), appt_date) - case when day(dob) > day(appt_date) 1 else 0 end), (dateadd(yy, (datediff(yy, dob, appt_date) - case when (month(dob) > month(appt_date)) or (month(dob) = month(appt_date) , day(dob) > day(appt_date)) 1 else 0 end), dob)))), appt_date)))) patage, pt.org_id, pt.address1 pataddress, pt.last_name lastname,pt.first_name firstname, pt.middle_name middlename, phy.last_name + '', '' + phy.first_name physician, dbo.formatdate(appt.appt_date,''mm/dd/yyyy'') collecteddate, (case when substring(start_time,0,charindex('':'', start_time)) < 12 convert(varchar,start_time) + convert(varchar,'' am'') else' +convert(varchar,@starttime)+ '+ substring(start_time,3,6) + '' pm'' end) collectedtime, (case when pt.gender =''1'' ''m'' else ''f'' end) gender,pt.city,lst.state_short_name state ' + (case when @specimenid = 0 ', 0 speciid' else ', (case when tbl_pat.speci_id > 0 tbl_pat.speci_id else '''' end) speciid' end) + ' pt inner join appt on pt.pat_id = appt.pat_id inner join phy on appt.phy_id = phy.phy_id inner join lst on pt.state = lst.state_id ' + (case when @specimenid = 0 '' else 'left outer join tbl_pat on appt.appt_id = tbl_pat.speci_apptid' end) + ' (appt.appt_id = 112601) ' + (case when @specimenid = 0 '' else 'and (tbl_pat.speci_id = ' + convert(varchar, @specimenid) + ')' end) exec (@script)
can or correct script please
Comments
Post a Comment