how to fix insert into datetime column with value like ''2013-09-14 00:49:04.055' casuse truncation?

Question

table def'n:

CREATE TABLE "dbo"."Note_and_xcptn_log" (

"Valuation_Dt" DATE NOT NULL,
"Acct_grp_ID" INTEGER foreign key references dbo.AcctGroup(Acct_grp_ID) NOT NULL,
"Acct_ID" INTEGER foreign key references dbo.account(acct_id) NOT NULL,
"subAcct_ID" INTEGER foreign key references dbo.SubAcct(subAcct_ID) NOT NULL,
"Exception_flg" char(1) NULL,
"broker_notified_dt" DATE NULL,
"Corrected_dt" DATE NULL,
"Note" char(1) NULL,
"time_stamp" DateTime NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT "Pk_NOTE_ValnDt_subAcctID" PRIMARY KEY ( "Valuation_Dt" ASC, "Acct_ID" ASC, "subAcct_ID" ASC )

)

sample insert:

INSERT INTO "dbo"."Note_and_xcptn_log" ("Valuation_Dt","Acct_grp_ID","Acct_ID","subAcct_ID","Exception_flg","broker_notified_dt","Corrected_dt","Note","time_stamp") VALUES('1753-09-14',9999,999999,-2147483648,NULL,NULL,NULL,'Test','2013-09-14 00:49:04.055');

error:
Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.

Answer 1

Hi @G. Gunn - fs ,

The truncated column seems to be the "Note" column, because the length of the column in your table definition is only char(1), but the length of the value Test you inserted is greater than char(1), so the data will be truncated. You can recreate the table to change the length of the "Note" column:

    CREATE TABLE "dbo"."Note_and_xcptn_log" (  
      
    "Valuation_Dt" DATE NOT NULL,  
    "Acct_grp_ID" INTEGER foreign key references dbo.AcctGroup(Acct_grp_ID) NOT NULL,  
    "Acct_ID" INTEGER foreign key references dbo.account(acct_id) NOT NULL,  
    "subAcct_ID" INTEGER foreign key references dbo.SubAcct(subAcct_ID) NOT NULL,  
    "Exception_flg" char(1) NULL,  
    "broker_notified_dt" DATE NULL,  
    "Corrected_dt" DATE NULL,  
    "Note" char(15) NULL,  
    "time_stamp" DateTime NOT NULL DEFAULT CURRENT_TIMESTAMP,  
    CONSTRAINT "Pk_NOTE_ValnDt_subAcctID" PRIMARY KEY ( "Valuation_Dt" ASC, "Acct_ID" ASC, "subAcct_ID" ASC )  
    )  

Or use ALTER TABLE to change the column definition:

    ALTER TABLE "dbo"."Note_and_xcptn_log"  
    ALTER COLUMN "Note" CHAR(15) ;  
    GO  

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.