Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

Motlatjo

Programmer
Oct 11, 2011
76
ZA
Hi Guys

The query below works fine in MSS Management Studio, but when i try to run this query on the report, it returns "Conversion failed when converting date and/or time from character to string"
All the date fields used here are of DateTime datatype
Please help

Thank you in advance


declare @reportFrom varchar(10),
@reportTo varchar(10),
@agentID varchar(10),
@hourFrom varchar(10),
@hourTo varchar(10),
@queue int

set @reportFrom = '2013-07-17'
set @reportTo = '2013-07-17'
set @agentID = 's1028167'
set @hourFrom = '07:00'
set @hourTo = '16:30'
set @queue = 0


declare @reportFrom varchar(10),
@reportTo varchar(10),
@agentID varchar(10),
@hourFrom varchar(10),
@hourTo varchar(10),
@queue int

set @reportFrom = '2013-07-17'
set @reportTo = '2013-07-17'
set @agentID = 'motlatjo'
set @hourFrom = '07:00'
set @hourTo = '16:30'
set @queue = 0

SELECT Date
,Agent
,Queue
,Status
,Reason
,Time
,Case When DatePart(HH, endTime) = DATEPART(HH, @hourFrom) And
DatePart(HH,Time) <> DATEPART(HH, @hourFrom) Then
DATEDIFF(MS, Convert(Varchar(10), Cast(Time As DATE), 120) + ' ' + @hourFrom + ':00.000', EndTime) END TimeSpend

,Timestamp
,TotalTime
,version
From AgentTimeDetail
Where Agent in (@AgentID)
And Date between @reportFrom and @reportTo
And Time < @reportFrom + ' ' + @hourTo + ':00.000'
And EndTime >= @reportTo + ' ' + @hourFrom + ':00.000'

Thank you
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top