Post reply

conversion error data type - Error msg inserting

  • July 24, 2014 at 12:19 pm

    #310305

    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

  • July 24, 2014 at 12:37 pm

    #1732173

    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
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply