SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 solved
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

saidev
Posting Yak Master

101 Posts

Posted - 09/21/2006 :  19:48:34  Show Profile
solved

Edited by - saidev on 09/21/2006 20:14:54

tkizer
Almighty SQL Goddess

USA
35932 Posts

Posted - 09/21/2006 :  20:04:01  Show Profile  Visit tkizer's Homepage
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
Go to Top of Page

timmy
Flowing Fount of Yak Knowledge

Australia
1242 Posts

Posted - 09/21/2006 :  20:04:45  Show Profile  Visit timmy's Homepage
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....
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 09/21/2006 :  20:26:05  Show Profile
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35932 Posts

Posted - 09/21/2006 :  20:30:18  Show Profile  Visit tkizer's Homepage


Tara Kizer
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17428 Posts

Posted - 09/21/2006 :  21:42:50  Show Profile
How did you get that ? Is there an audit trail on the thread table ?


KH

Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 09/21/2006 :  22:27:43  Show Profile
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17428 Posts

Posted - 09/21/2006 :  22:32:35  Show Profile
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


KH

Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/22/2006 :  09:28:30  Show Profile
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29908 Posts

Posted - 09/22/2006 :  09:35:37  Show Profile  Visit SwePeso's Homepage
Hey! Another fan of Monty


Peter Larsson
Helsingborg, Sweden
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000