May 26, 2010 at 2:40 am
Hi Guys,
I have the following quer which is giving me some performance issues:
declare @startDate datetime
set @startDate = '2009-01-01 00:00:00.000'
declare @endDate datetime
set @endDate = '2009-12-31 00:00:00.000'
declare @feeEarner varchar(3)
set @feeEarner = ''
declare @WipCode varchar(5)
set @WipCode = ''
declare @AcCode varchar(8)
set @AcCode = ''
declare @Country varchar(2)
set @Country = ''
select
o.openitemno,
convert(varchar, o.itemdate, 103) as BilledDate,
localtransvalue =
case
when o.billpercentage <> 100 then
case
when wh.transtype = 511 then convert(decimal(10, 2), ((wip_table.localtransvalue / 100) * o.billpercentage) / -1)
when right(o.openitemno, 2) = 'CN' then convert(decimal(10, 2), ((wip_table.localtransvalue / 100) * o.billpercentage))
else convert(decimal(10, 2), ((wip_table.localtransvalue / 100) * o.billpercentage))
end
else
case
when wh.transtype = 511 then convert(decimal(10, 2), wip_table.localtransvalue / -1)
when right(o.openitemno, 2) = 'CN' then convert(decimal(10, 2), wip_table.localtransvalue)
else convert(decimal(10, 2), wip_table.localtransvalue)
end
end,
totalunits =
case
when o.billpercentage <> 100 then
case
when wh.transtype = 511 then convert(decimal(10, 1), ((wip_table.totalunits / 100) * o.billpercentage) / -1)
when right(o.openitemno, 2) = 'CN' then convert(decimal(10, 1), ((wip_table.totalunits / 100) * o.billpercentage))
else convert(decimal(10, 1), ((wip_table.totalunits / 100) * o.billpercentage))
end
else
case
when wh.transtype = 511 then convert(decimal(10, 1), wip_table.totalunits / -1)
when right(o.openitemno, 2) = 'CN' then convert(decimal(10, 1), wip_table.totalunits)
else convert(decimal(10, 1), wip_table.totalunits)
end
end,
wip_table.wipcode,
c.irn,
e.abbreviatedname
from openitem o
inner join workhistory wh on wh.reftransno = o.itemtransno
inner join cases c on c.caseid = wh.caseid
inner join employee e on e.employeeno = wh.employeeno
inner join debtorhistory d on d.openitemno = o.openitemno and d.movementclass = 1
inner join name n on n.nameno = d.acctdebtorno
inner join (
select distinct(wh2.transno), wh2.wipcode, wh2.totalunits, wh2.localtransvalue
from workhistory wh2
inner join wiptemplate wp on wp.wipcode = wh2.wipcode and wp.wiptypeid = 'SERCHG'
where wh2.transtype in (400, 402, 406, 1000, 1003, 1001)
) as wip_table on wip_table.transno = wh.transno
where o.itemdate between @startDate and @endDate
and e.abbreviatedname like case when @feeEarner = '' then '%%' else @feeEarner end
and wh.wipcode like case when @WipCode = '' then '%%' else @WipCode end
and n.namecode like case when @ACCode = '' then '%%' else @ACCode end
and n.nationality like case when @Country = '' then '%%' else @Country end
and wh.transtype in (510, 511, 512, 516)
and o.status <> 9
order by o.itemdate asc, c.irn asc
I can run it fine on our test server, but on our live server I get the following error:
Msg 242, Level 16, State 3, Line 4
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
I'm a bit confused as to why this is happening. The date settings on the servers appear to be the same, and the test server has an identical copy of the DB.
Any ideas of why this may be happening?
May 26, 2010 at 3:00 am
declare @startDate datetime
set @startDate = '2009-01-01 00:00:00.000'
declare @endDate datetime
set @endDate = '2009-12-31 00:00:00.000'
declare @feeEarner varchar(3)
set @feeEarner = ''
declare @WipCode varchar(5)
set @WipCode = ''
declare @AcCode varchar(8)
set @AcCode = ''
declare @Country varchar(2)
set @Country = ''
select
o.openitemno,
convert(varchar(10), o.itemdate, 103) as BilledDate,
localtransvalue =
case
when o.billpercentage <> 100 then
case
when wh.transtype = 511 then convert(decimal(10, 2), ((wip_table.localtransvalue / 100) * o.billpercentage) / -1)
when right(o.openitemno, 2) = 'CN' then convert(decimal(10, 2), ((wip_table.localtransvalue / 100) * o.billpercentage))
else convert(decimal(10, 2), ((wip_table.localtransvalue / 100) * o.billpercentage))
end
else
case
when wh.transtype = 511 then convert(decimal(10, 2), wip_table.localtransvalue / -1)
when right(o.openitemno, 2) = 'CN' then convert(decimal(10, 2), wip_table.localtransvalue)
else convert(decimal(10, 2), wip_table.localtransvalue)
end
end,
totalunits =
case
when o.billpercentage <> 100 then
case
when wh.transtype = 511 then convert(decimal(10, 1), ((wip_table.totalunits / 100) * o.billpercentage) / -1)
when right(o.openitemno, 2) = 'CN' then convert(decimal(10, 1), ((wip_table.totalunits / 100) * o.billpercentage))
else convert(decimal(10, 1), ((wip_table.totalunits / 100) * o.billpercentage))
end
else
case
when wh.transtype = 511 then convert(decimal(10, 1), wip_table.totalunits / -1)
when right(o.openitemno, 2) = 'CN' then convert(decimal(10, 1), wip_table.totalunits)
else convert(decimal(10, 1), wip_table.totalunits)
end
end,
wip_table.wipcode,
c.irn,
e.abbreviatedname
from openitem o
inner join workhistory wh on wh.reftransno = o.itemtransno
inner join cases c on c.caseid = wh.caseid
inner join employee e on e.employeeno = wh.employeeno
inner join debtorhistory d on d.openitemno = o.openitemno and d.movementclass = 1
inner join name n on n.nameno = d.acctdebtorno
inner join (
select distinct(wh2.transno), wh2.wipcode, wh2.totalunits, wh2.localtransvalue
from workhistory wh2
inner join wiptemplate wp on wp.wipcode = wh2.wipcode and wp.wiptypeid = 'SERCHG'
where wh2.transtype in (400, 402, 406, 1000, 1003, 1001)
) as wip_table on wip_table.transno = wh.transno
where o.itemdate between @startDate and @endDate
and e.abbreviatedname like case when @feeEarner = '' then '%%' else @feeEarner end
and wh.wipcode like case when @WipCode = '' then '%%' else @WipCode end
and n.namecode like case when @ACCode = '' then '%%' else @ACCode end
and n.nationality like case when @Country = '' then '%%' else @Country end
and wh.transtype in (510, 511, 512, 516)
and o.status <> 9
order by o.itemdate asc, c.irn asc
Could you try this one now
May 26, 2010 at 3:18 am
Out of interest what did you change?
I think I may have solved this anyway. The user in SQL had a language of British English set. It seems to be working fine now after I changed this to English.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply