Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

conversion error data type - Error msg inserting Expand / Collapse
Author
Message
Posted Thursday, July 24, 2014 12:19 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 3:02 PM
Points: 171, Visits: 462
When I run the Select statement without the insert, it does not return any records. I don't know why I get the error msg below.

I get the following error msg when running the code below:
Msg 257, Level 16, State 3, Line 3
Implicit conversion from data type smalldatetime to int is not allowed. Use the CONVERT function to run this query.



If (Select top 1 Tab03ID From SubQryTab05_SubTab02forAppend) IS NOT NULL
BEGIN
INSERT INTO Tab06_SubTab02 (
GroupID, AssignedAuditors, Include, InTrans, Pricing, IncludeSample, VndNbrAlt, ItemClientStyleKey, CltStyle, RecNo, PdItmNetCst, PdQty, OutBal, PONbr, PODt, POItmGrsCst, POItmNetCst, POQty, InvNbr, InvDt, InvShpDt
, InvItmGrsCst, InvItmNetCst, InvQty, RcvNbr, RcvDt, RcvItmGrsCst, RcvItmNetCst, RcvQty, DptNbr, MerchEffDt, MerchEffDtCode, PPInvDt, LastUpdatedBy, LastUpdatedDate, AuditPeriodKey, FactBalancingDetailKey, ActiveFlag, LoadDt, MoveToWS, Sample
, Comments, Tab05_SubTab02ID, Tab03ID, Tab02ID )
SELECT
'130400017', 'abenit01', Include, InTrans, Pricing, IncludeSample, VndNbrAlt, ItemClientStyleKey, CltStyle, RecNo, PdItmNetCst, PdQty, OutBal, PONbr, PODt, POItmGrsCst, POItmNetCst, POQty, InvNbr, InvDt, InvShpDt
, InvItmGrsCst, InvItmNetCst, InvQty, RcvNbr, RcvDt, RcvItmGrsCst, RcvItmNetCst, RcvQty, DptNbr, MerchEffDt, MerchEffDtCode, PPInvDt, LastUpdatedBy, LastUpdatedDate, AuditPeriodKey, FactBalancingDetailKey, ActiveFlag, LoadDt, MoveToWS, Sample
, Comments, Tab05_SubTab02ID, Tab03ID, Tab02ID
FROM SubQryTab05_SubTab02forAppend
END




Post #1595983
Posted Thursday, July 24, 2014 12:37 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:14 PM
Points: 1,069, Visits: 3,329
In the table Tab06_SubTab02, you have field defined as an integer.
The corresponding field in the select statement is a smalldatetime.

Even though there are no rows returned, SQL still compiles the query and throws a compile time error.

You need to match up the fields in the insert to the fields in the select and compare the data types. An integer field cannot be populated by a smalldatetime field.

As an easier example, this will throw the same error:

DECLARE @Tab TABLE
(
IntegerField int
)

DECLARE @smalldatetime smalldatetime

INSERT INTO @Tab(IntegerField)
SELECT @smalldatetime


Michael L John
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Post #1595987
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse