|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
 Posted - 09/21/2006 : 20:26:05
|
For those of you that missed it before he deleted his post.
Topic: syntax error converting datetime from character st
Hi,
I am getting this error "Syntax error converting datetime from character string" when i try to execute this Stored Procedure.
Can you guys help me what was wrong.
THanks,
CREATE PROCEDURE msp_UpdateEIImportStatus
@mBatchID Int
AS
DELETE FROM tblTempDRSRecord
WHERE fkBatchID = @mBatchID /*Variable*/
insert into tblTempDRSRecord
(fkBatchID,
fkRepresentedEntity,
TrafficSystem,
RelativeLineNumber,
ProductName,
MakeGoodDate,
DateRan,
TimeRan,
SpotLength,
OriginalSpotRate,
AdjustedSpotRate,
RunNumber,
AdjustmentAmount,
InvoiceNumber,
fkSpotCode,
Advertiser,
Agency,
Timetxt,
ProgramBuyNumber,
ReconciliationRemarks,
NumberOfLines,
MakegoodGroup,
ftsordernumber)
select
@mBatchID,
(select pkid from tblClient where DRSDiskLabel=e.CallLetters) fkRepresentedEntity ,
'TrafficSystem' =
CASE
WHEN e.CallLetters = 'CMST' THEN ltrim(e.AdvertiserNumber)
ELSE ltrim(e.AdvertiserNumber) + '-' + ltrim(e.OrderNumber)
END,
CONVERT(int,e.LineNumber) 'RelativeLineNumber',
e.CopyID,
'MakeGoodDate' =
CASE
WHEN ltrim(e.MakeGoodDate)='0' THEN Null
WHEN ltrim(e.MakeGoodDate)='000000' THEN Null
ELSE convert(datetime, substring(e.MakeGoodDate,1,2) + '/' + substring(e.MakeGoodDate,3,2) + '/' + substring(e.MakeGoodDate,5,2))
END,
CONVERT(datetime, substring(e.RunDate,1,2) + '/' + substring(e.RunDate,3,2) + '/' + substring(e.RunDate,5,2)),
'RunTime' =
CASE
WHEN e.RunTime is null THEN Null
WHEN e.RunTime='' THEN Null
ELSE '12/30/1899 ' + substring(e.RunTime,1,2) + ':' + substring(e.RunTime,3,2) + ':' + substring(e.RunTime,5,2) + substring(e.RunTime,7,1) + 'm'
END,
CONVERT(int,e.RunLength),
CONVERT(money,e.CostPerSpot)/100 'OriginalSpotRate',
CONVERT(money,e.CostPerSpot)/100 'AdjustedSpotRate',
null,
CONVERT(money,e.MGDR)/100+CONVERT(money,e.MGCR)/100 'AdjustmentAmount',
null,
'fkSpotCode' =
CASE
WHEN e.RunCode='Y' THEN 'AS ORDERED'
ELSE 'NO RUN'
END,
CASE
WHEN e.AdvertiserAgencyType=2 THEN substring(e.AdvertiserAgencyName,1,20)
ELSE ' '
END,
CASE
WHEN e.AdvertiserAgencyType=1 THEN substring(e.AdvertiserAgencyName,1,20)
ELSE ' '
END,
CASE
WHEN e.RunTime is null THEN Null
WHEN e.RunTime='' THEN Null
ELSE
CASE
WHEN substring(e.RunTime,7,1)='a' THEN substring(e.RunTime,1,2) + substring(e.RunTime,3,2) +substring(e.RunTime,5,2)
WHEN substring(e.RunTime,7,1)='p' THEN convert(varchar(2),convert(int,substring(e.RunTime,1,2))+12) + substring(e.RunTime,3,2) + substring(e.RunTime,5,2)
END
END,
substring(e.AdvertiserAgencyName,31,30),
substring(e.AdvertiserAgencyName,61,20),
convert(int,substring(e.AdvertiserAgencyName,81,3)),
convert(int,substring(e.AdvertiserAgencyName,84,3)),
OrderNumber
FROM tblTempEIImport e
convert(int,substring(Timetxt,5,2))>=30 and fkRepresentedEntity in (select pkid from tblClient where TrafficSystemFlag='R')
UPDATE tblTempDRSRecord
set AdjustedSpotRate=OriginalSpotRate+AdjustmentAmount
where AdjustmentAmount is not null /*Make Good*/
UPDATE tblTempDRSRecord
set TimeRan=Null, Timetxt=Null,
--OriginalSpotRate=0,
AdjustedSpotRate=0,AdjustmentAmount=0
where fkSpotCode = 'NO RUN'
UPDATE tblTempDRSRecord
SET tblTempDRSRecord.fkContract=tblContract.pkid,
tblTempDRSRecord.JoinStatus=1
from tblTempDRSRecord,tblContract WHERE tblTempDRSRecord.TrafficSystem = tblContract.fkTrafficSystem
and tblTempDRSRecord.fkRepresentedEntity = tblContract.fkClient
AND tblTempDRSRecord.fkBatchID = @mBatchID /*Variable*/
AND tblContract.fkContractType='UNWIRED'
UPDATE tblTempDRSRecord
SET tblTempDRSRecord.fkContract=tblContract.pkid,tblTempDRSRecord.JoinStatus=1
from tblTempDRSRecord,tblContract
WHERE tblTempDRSRecord.fkContract is null
and '00' + tblTempDRSRecord.TrafficSystem = tblContract.fkTrafficSystem
and tblTempDRSRecord.fkRepresentedEntity = tblContract.fkClient
AND tblTempDRSRecord.fkBatchID = @mBatchID /*Variable*/
AND tblContract.fkContractType='UNWIRED'
UPDATE tblTempDRSRecord
SET tblTempDRSRecord.JoinStatus=2,
tblTempDRSRecord.fkDetail=tblDetail.pkid
FROM tblTempDRSRecord,tblDetail WHERE tblTempDRSRecord.JoinStatus=1 AND tblTempDRSRecord.fkContract= tblDetail.fkContract
AND tblTempDRSRecord.RelativeLineNumber=tblDetail.line
AND tblTempDRSRecord.fkBatchID = @mBatchID /*Variable*/
UPDATE tblTempDRSRecord
SET tblTempDRSRecord.fkImportStatus=9
WHERE tblTempDRSRecord.JoinStatus=0
UPDATE tblTempDRSRecord
SET tblTempDRSRecord.fkImportStatus=8
WHERE tblTempDRSRecord.JoinStatus=1
/*Update Already posted spots */
UPDATE tblTempDRSRecord
SET tblTempDRSRecord.fkImportStatus=4, tblTempDRSRecord.fkSpot=tblSpot.pkid
FROM tblTempDRSRecord,tblSpot
WHERE tblSpot.fkDetail=tblTempDRSRecord.fkDetail
AND tblSpot.DateActual = tblTempDRSRecord.DateRan
AND convert(varchar (5),tblSpot.TimeActual,8) =convert(varchar (5),tblTempDRSRecord.TimeRan,8)
AND tblSpot.RateActual = tblTempDRSRecord.AdjustedSpotRate
AND tblSpot.Postedflag=1
AND tblTempDRSRecord.fkImportStatus=Null
AND tblTempDRSRecord.JoinStatus=2
AND tblTempDRSRecord.fkBatchID=@mBatchId
/*Update Already invoiced spots */
UPDATE tblTempDRSRecord
SET tblTempDRSRecord.fkImportStatus=5, tblTempDRSRecord.fkSpot=tblSpot.pkid
FROM tblTempDRSRecord,tblSpot
WHERE tblSpot.fkDetail=tblTempDRSRecord.fkDetail
AND tblSpot.DateActual = tblTempDRSRecord.DateRan
AND convert(varchar (5),tblSpot.TimeActual,8) =convert(varchar (5),tblTempDRSRecord.TimeRan,8)
AND tblSpot.RateActual = tblTempDRSRecord.AdjustedSpotRate
AND tblSpot.PostedFlag=0
AND tblSpot.fkInvoice<>Null
AND tblTempDRSRecord.fkImportStatus=Null
AND tblTempDRSRecord.JoinStatus=2
AND tblTempDRSRecord.fkBatchID=@mBatchId
/*Update perfect match */ UPDATE tblTempDRSRecord
SET tblTempDRSRecord.fkImportStatus=1, tblTempDRSRecord.fkSpot=tblSpot.pkid
FROM tblTempDRSRecord,tblSpot WHERE tblSpot.fkDetail=tblTempDRSRecord.fkDetail
AND tblSpot.DateActual = tblTempDRSRecord.DateRan
AND convert(varchar (5),tblSpot.TimeActual,8) =convert(varchar (5),tblTempDRSRecord.TimeRan,8)
AND tblSpot.PostedFlag=0
AND tblSpot.fkInvoice=Null
AND tblTempDRSRecord.fkImportStatus=Null
AND tblTempDRSRecord.fkBatchID=@mBatchId
/* Update make good from the spot table. Each makegood line has only one item and it is make good */
update tblTempDRSRecord
set fkSpotCode='MAKE GOOD'
from tblTempDRSRecord t, tblSpot s
where t.fkSpot=s.pkid
and s.fkSpotCode ='MAKE GOOD'
and t.fkSpotCode='AS ORDERED'
update tblTempDRSRecord
set fkSpotCode='MG-NO RUN'
from tblTempDRSRecord t, tblSpot s
where t.fkSpot=s.pkid
and s.fkSpotCode ='MAKE GOOD'
and t.fkSpotCode='NO RUN'
--SQL SCRIPT TO GET PKID OF PROGRAMBUYNUMBER--
--1. Do the matching
UPDATE tblTempDRSRecord SET ProgramBuyNumber = rtrim(ProgramBuyNumber)
UPDATE tblTempDRSRecord
SET fkProgramBuyNumber = (select pkid from tblProgramBuyNumbers WHERE description = t.ProgramBuyNumber)
FROM tblTempDRSRecord t
--2. INSERT INTO tblProgramBuyNumbers VALUES THAT ARE NULL
insert into tblProgramBuyNumbers (description)
SELECT DISTINCT ProgramBuyNumber
FROM tblTempDRSRecord where fkProgramBuyNumber is null
--1. Do the matching
UPDATE tblTempDRSRecord
SET fkProgramBuyNumber = (select pkid from tblProgramBuyNumbers WHERE description = t.ProgramBuyNumber)
FROM tblTempDRSRecord t WHERE fkProgramBuyNumber is null
/* Update Import Status field*/
UPDATE tblBatchID
SET Status=1
where pkid=@mBatchID /*Variable*/
GO
|
 |
|
solved
Forum Locked
