solved - SQL Server Forums

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

Old Forums

CLOSED - General SQL Server

solved

Author

Topic

saidev
Posting Yak Master

101 Posts

Posted - 2006-09-21 : 19:48:34

solved

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-21 : 20:04:01

One of your converts is wrong. Posting the line number where the error occurred would help us help you.

I guess you are ignoring our repeated posts to use datetime for your date types instead of varchar for your date and time data.

Tara Kizer

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-09-21 : 20:04:45

Have you tried breaking it down to narrow down where the error is occuring?

It's easier for us if you at least attempt to solve it yourself....

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-21 : 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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-21 : 20:30:18

Tara Kizer

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-21 : 21:42:50

How did you get that ? Is there an audit trail on the thread table ?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-21 : 22:27:43

quote:
Originally posted by khtan

How did you get that ? Is there an audit trail on the thread table ?

KH

I just happemed to have the post still open.

CODO ERGO SUM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-21 : 22:32:35

quote:
Originally posted by Michael Valentine Jones

quote:
Originally posted by khtan

How did you get that ? Is there an audit trail on the thread table ?

KH

I just happemed to have the post still open.

CODO ERGO SUM

Ha ! Who would expect that

X002548
Not Just a Number

15586 Posts

Posted - 2006-09-22 : 09:28:30

No one! In fact, No one expects the Spanish inquisition! And if they do....

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-22 : 09:35:37

Hey! Another fan of Monty

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources