Conversion failed when converting datetime

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Conversion failed when converting datetime

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2007-12-14 : 19:31:52

Msg 241, Level 16, State 1, Line 11
Conversion failed when converting datetime from character string.

I must be blind this afternoon and cant figure this one out .... TGIF

SET NOCOUNT ON

DECLARE @startdate DATETIME,
@enddate DATETIME,
@sortby VARCHAR(50)

set @startdate = ISNULL('12/14/2007','1/1/1900')
set @enddate = dateadd(day,1,ISNULL('12/14/2007','1/1/3000'))
set @sortby = convert(varchar(20),'PatientName')

select distinct
a.appointmentsID,at.duration,
a.ApptStart,
a.Createdby as ApptCreatedByUser,
a.Created as ApptDateCreated,
ISNULL(a.TIcketNumber,'No Ticket Number') as TicketNumber,
aty.Name as ApptTypeName,
pp.last +', '+pp.first as PatientName,
arr.created as ArrivalTime,
arr.createdBy as ArrivedByUser,
(select top 1 cplt.created
from activitylog cplt
where arr.created < cplt.created and a.appointmentsID = cplt.recordID
order by cplt.activitylogID asc
) as CompletedTime,
(select top 1 cplt.createdby
from activitylog cplt
where arr.created < cplt.created and a.appointmentsID = cplt.recordID
order by cplt.activitylogID asc
) as CompletedByUser,
Astat.description as CurrentStatus,
fac.listname as Facility,
dr.listname as ApptDoctor,
res.listname as ApptResource,
case @sortby
when 'Doctor' then dr.listname
when 'PatientName' then pp.last +', '+pp.first
when 'Appt Start' then a.apptstart
when 'Arrival Time' then arr.created
when 'Completed Time' then cplt.created
else a.apptstart
end as Sortby
INTO #temp
from appointments a
inner join patientprofile pp on a.ownerID = pp.patientprofileID
left join appttype aty on a.appttypeID = aty.appttypeID
left join activitylog arr on a.appointmentsID = arr.recordID and arr.Value2 ='Arrived'
left join activitylog cplt on a.appointmentsID = cplt.recordID and cplt.Value2 ='Arrived'
left join doctorfacility dr on a.doctorID = dr.doctorfacilityID
left join doctorfacility res on a.resourceID = res.doctorfacilityID
left join doctorfacility fac on a.facilityID = fac.doctorfacilityID
left join medlists astat on a.apptStatusMID = astat.medlistsID
LEFT OUTER JOIN ApptType at ON a.ApptTypeId = at.ApptTypeId
where a.apptstart >= @startdate
and a.apptstart < @enddate
and ISNULL(a.canceled,0) = 0
and a.apptkind = 1
AND --Filter on doctor
(
(NULL IS NOT NULL AND a.doctorID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on facility
(
(NULL IS NOT NULL AND a.FacilityID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on Appt Type
(
(NULL IS NOT NULL AND a.ApptTypeID IN (NULL)) OR
(NULL IS NULL)
)
AND -- If include arr/completed reports is checked
(
('0' = '1' and arr.created IS NOT NULL and cplt.created IS NOT NULL)
OR ('0' = '0')
)
order by sortby

select t.appointmentsID, t.apptstart,t.duration,
ApptCreatedByUser,
ApptDateCreated,
t.TicketNumber,
ApptTypeName,
PatientName,
ArrivalTime,
ArrivedByUser,
CompletedTime,
CompletedByUser,
CurrentStatus,
a.notes as ApptNotes,
datediff(minute,ArrivalTIme,CompletedTime) as Minutes,
Facility,
ApptDoctor, ApptResource,
Sortby

from #temp t
inner join appointments a on t.appointmentsID =a.appointmentsID

drop table #temp

JeffS23
Posting Yak Master

212 Posts

Posted - 2007-12-14 : 20:04:27

disregard ....this fixed me.

case @sortby
when 'Doctor' then dr.listname
when 'PatientName' then pp.last +', '+pp.first
when 'Appt Start' then CONVERT(VARCHAR(10),a.apptstart,120)
when 'Arrival Time' then CONVERT(VARCHAR(10),arr.created,120)
when 'Completed Time' then CONVERT(VARCHAR(10),cplt.created,120)
else CONVERT(VARCHAR(10),a.apptstart,120)
end as Sortby

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources